MySQL的基本操作
\G 表示查询结果进行按列打印,横向坐标纵向打印
show database; 列出数据库列表
use 数据库名称; 选择要操作的数据库
show tables;列出数据库所有表
show columns from 表名称;显示数据表的属性
show index from 数据表;显示数据表的详细索引信息
MySQL数据库的操作创建数据库
1 create database 数据库名称; 创建数据库
删除数据库
1 drop database 数据库名称;删除数据库
选择数据库
1 use 数据库名称;选择数据库
查询全部数据库
1 show databases; 查询全部数据库 2 3 mysql> show databases; 4 +--------------------+ 5 | Database | 6 +--------------------+ 7 | information_schema | 8 | mysql | 9 | performance_schema | 10 | sys | 11 +--------------------+ 12 4 rows in set (0.00 sec) 13
查询创建数据库的语句
1 mysql> show create database xiaole; 2 +----------+-----------------------------------------------------------------+ 3 | Database | Create Database | 4 +----------+-----------------------------------------------------------------+ 5 | xiaole | CREATE DATABASE `xiaole` /*!40100 DEFAULT CHARACTER SET utf8 */ | 6 +----------+-----------------------------------------------------------------+ 7 1 row in set (0.00 sec) 8 9 Create Database 里面就是创建数据库的语句 10 11 12 13
MySQL用户管理
1 use mysql; 切换到mysql数据库 2 3 4 #添加用户 5 MariaDB [(none)]> create user 'xiaole'@'%' identified by 'xiaole521'; 6 Query OK, 0 rows affected (0.00 sec) 7 8 xiaole 是用户名 9 % 是指定登录 %是代表全部地址都可以登录 localhost是本地登录 10 xiaole521 是密码 11 12 13 MariaDB [mysql]> flush privileges; 刷新权限 14 Query OK, 0 rows affected (0.00 sec) 15 16 17 查询用户 18 select host, user, password from user where user='xiaole'; 19 20 21 #删除用户 22 MariaDB [mysql]> delete from user where user='xiaole'; 23 Query OK, 1 row affected (0.00 sec) 24 25 xiaole是用户 26 27
数据表的操作
创建数据表
1 create table if not exists `name`( 2 `id` int auto_increment, 3 `name` varchar(200) not null comment '名称', 4 `age` int(3) not null comment '年龄', 5 primary key (`id`) 6 )engine=InnoDB default charset=utf8; 7 8 CREATE TABLE `user` ( 9 `id` INT(11) AUTO_INCREMENT, 10 `user` VARCHAR(16) NOT NULL, 11 `password` VARCHAR(16) NOT NULL , 12 PRIMARY KEY (`id`) 13 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
name是表名
id是字段
auto_increment是设置自动增长 从1开始增长
primary key是设置主键
engine是设置存储引擎
charset是设置编码修改表
向数据表添加字段
1 alter table 表名 add column 字段名称 varchar(16) not null comment '这是我添加的字段'; 2 3 mysql> alter table user add column userxiaoletest varchar(16) not null comment '这是我添 加的字段'; 4 Query OK, 0 rows affected (0.02 sec) 5 Records: 0 Duplicates: 0 Warnings: 0 6 7 mysql> show full columns from user; 8 +----------------+-------------+-----------------+------+-----+---------+-------------- --+---------------------------------+--------------------------------+ 9 | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | 10 +----------------+-------------+-----------------+------+-----+---------+-------------- --+---------------------------------+--------------------------------+ 11 | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | 12 | user | varchar(16) | utf8_general_ci | NO | | NULL | | select,insert,update,references | 用户名 | 13 | password | varchar(16) | utf8_general_ci | YES | | 123456 | | select,insert,update,references | 密码如果不填默认123456 | 14 | usertest | varchar(16) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | 15 | userxiaole | varchar(16) | utf8_general_ci | NO | | NULL | | select,insert,update,references | 这是我添加的字段 | 16 | userxiaoletest | varchar(16) | utf8_general_ci | NO | | NULL | | select,insert,update,references | 这是我添加的字段 | 17 +----------------+-------------+-----------------+------+-----+---------+-------------- --+---------------------------------+--------------------------------+ 18 6 rows in set (0.02 sec) 19
修改数据表结构
1 alter table 表名 modify 字段 int not null comment '这是我修改的数据表'; 2 3 mysql> alter table user modify userxiaoletest int not null comment '这是我修改的数据表'; 4 Query OK, 0 rows affected (0.01 sec) 5 Records: 0 Duplicates: 0 Warnings: 0 6 7 mysql> show full columns from user; 8 +----------------+-------------+-----------------+------+-----+---------+-------------- --+---------------------------------+--------------------------------+ 9 | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | 10 +----------------+-------------+-----------------+------+-----+---------+-------------- --+---------------------------------+--------------------------------+ 11 | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | 12 | user | varchar(16) | utf8_general_ci | NO | | NULL | | select,insert,update,references | 用户名 | 13 | password | varchar(16) | utf8_general_ci | YES | | 123456 | | select,insert,update,references | 密码如果不填默认123456 | 14 | usertest | varchar(16) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | 15 | userxiaole | varchar(16) | utf8_general_ci | NO | | NULL | | select,insert,update,references | 这是我添加的字段 | 16 | userxiaoletest | int(11) | NULL | NO | | NULL | | select,insert,update,references | 这是我修改的数据表 | 17 +----------------+-------------+-----------------+------+-----+---------+-------------- --+---------------------------------+--------------------------------+ 18 6 rows in set (0.01 sec) 19
删除表中字段
1 alter table 表名称 drop column 要删除的字段; 2 3 mysql> alter table user drop column userxiaoletest; 4 Query OK, 0 rows affected (0.02 sec) 5 Records: 0 Duplicates: 0 Warnings: 0 6 7 mysql> show full columns from user; 8 +------------+-------------+-----------------+------+-----+---------+----------------+- --------------------------------+--------------------------------+ 9 | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | 10 +------------+-------------+-----------------+------+-----+---------+----------------+- --------------------------------+--------------------------------+ 11 | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | 12 | user | varchar(16) | utf8_general_ci | NO | | NULL | | select,insert,update,references | 用户名 | 13 | password | varchar(16) | utf8_general_ci | YES | | 123456 | | select,insert,update,references | 密码如果不填默认123456 | 14 | usertest | varchar(16) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | 15 | userxiaole | varchar(16) | utf8_general_ci | NO | | NULL | | select,insert,update,references | 这是我添加的字段 | 16 +------------+-------------+-----------------+------+-----+---------+----------------+- --------------------------------+--------------------------------+ 17 5 rows in set (0.01 sec) 18 19 mysql> 20
更改表名
1 rename table 要修改的表名 to 修改成的名字; 2 3 mysql> rename table user to username; 4 Query OK, 0 rows affected (0.01 sec) 5 6 mysql> show tables; #查询全部表 7 +--------------------+ 8 | Tables_in_test-txt | 9 +--------------------+ 10 | username | 11 +--------------------+ 12 1 row in set (0.01 sec) 13
删除数据表
1 drop table 要删除的数据表;
查询数据表结构
1 desc user; 查询表结构 2 3 mysql> desc user; 4 +----------+-------------+------+-----+---------+----------------+ 5 | Field | Type | Null | Key | Default | Extra | 6 +----------+-------------+------+-----+---------+----------------+ 7 | id | int(11) | NO | PRI | NULL | auto_increment | 8 | user | varchar(16) | NO | | NULL | | 9 | password | varchar(16) | YES | | 123456 | | 10 +----------+-------------+------+-----+---------+----------------+ 11 3 rows in set (0.01 sec) 12 13 show columns from 表名称; 14 mysql> show columns from user; 15 +----------+-------------+------+-----+---------+----------------+ 16 | Field | Type | Null | Key | Default | Extra | 17 +----------+-------------+------+-----+---------+----------------+ 18 | id | int(11) | NO | PRI | NULL | auto_increment | 19 | user | varchar(16) | NO | | NULL | | 20 | password | varchar(16) | YES | | 123456 | | 21 +----------+-------------+------+-----+---------+----------------+ 22 3 rows in set (0.01 sec) 23 24 25 show full columns from 表名称; 26 27 mysql> show full columns from user; 28 +----------+-------------+-----------------+------+-----+---------+----------------+--- ------------------------------+--------------------------------+ 29 | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | 30 +----------+-------------+-----------------+------+-----+---------+----------------+--- ------------------------------+--------------------------------+ 31 | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | 32 | user | varchar(16) | utf8_general_ci | NO | | NULL | | select,insert,update,references | 用户名 | 33 | password | varchar(16) | utf8_general_ci | YES | | 123456 | | select,insert,update,references | 密码如果不填默认123456 | 34 +----------+-------------+-----------------+------+-----+---------+----------------+--- ------------------------------+--------------------------------+ 35 3 rows in set (0.01 sec) 36 #full 是更详细的显示、 37 38 Key 表的索引 39 Collation 字符集和字符列排序规则 40 Privileges 权限 41 Comment 解释