sql语句的语义种类
DDL: 数据定义语言 Data Definition Language
DCL: 数据控制语言 Data Control Language
DML: 数据操作语言 Data Manipulate Language
DQL: 数据查询语言 Data Query Language
1.DDL: 数据定义语言 （create、drop）
1.create针对库的操作
1.语法
mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
2.创建库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> create SCHEMA db2;
Query OK, 1 row affected (0.00 sec)
3.建库时提示已存在
mysql> create database db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
mysql> create database IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> create database IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
4.查看建库语句
mysql> show create database db1;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
5.创建数据库并指定字符集
mysql> create database db3 charset utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db3;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
# 不指定校验规则默认就是 utf8_general_ci
mysql> create database db4 charset utf8;
Query OK, 1 row affected (0.00 sec)
6.删库
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
7.修改库的字符集
# 查看
mysql> show create database db2;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
# 修改
mysql> alter database db2 charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
# 再次查看
mysql> show create database db2;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
2.create针对表的操作
1.语法
mysql> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
2.建表
#1.进入一个库
mysql> use db2
Database changed
#2.查看当前所在库
mysql> select database();
+------------+
| database() |
+------------+
| db2 |
+------------+
1 row in set (0.00 sec)
#3.建表，建表最少有一列
mysql> create table tb1;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)
#4.查看表
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| tb1 |
+---------------+
1 row in set (0.00 sec)
mysql> desc tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
3.数据类型
int 整数 -2^31 - 2^31-1 (-2147483648 - 2147483647)
tinyint 最小整数 -128 - 127 #年龄 0 - 255
varchar 字符类型（变长） #身份证
char 字符类型（定长）
enum 枚举类型 #给它固定选项，只能选则选择项中的值 性别
datetime 时间类型 年月日时分秒
4.数据类型测试
# int类型
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)
mysql> insert tb1 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert tb1 values(11111111111);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert tb1 values(2147483647);
Query OK, 1 row affected (0.00 sec)
mysql> insert tb1 values(2147483648);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert tb1 values(-2147483648);
Query OK, 1 row affected (0.00 sec)
mysql> insert tb1 values(-2147483649);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
# enum类型
mysql> create table qiudao(id int,sex enum('nan','nv'));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into qiudao values(1,'nan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into qiudao values(1,'qiudao');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
5.建表测试
表名：student
id
name
age
gender
cometime
#1.建表
mysql> create table student(
-> id int,
-> name varchar(12),
-> age tinyint,
-> gender enum('M','F'),
-> cometime datetime);
Query OK, 0 rows affected (0.01 sec)
#2.插入数据
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.01 sec)
#3.查看数据
mysql> select * from student;
+------+--------+------+--------+---------------------+
| id | name | age | gender | cometime |
+------+--------+------+--------+---------------------+
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:04 |
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:08 |
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:09 |
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:10 |
+------+--------+------+--------+---------------------+
4 rows in set (0.00 sec)
6.建表数据属性
not null： #非空
primary key： #主键（唯一且非空的）
auto_increment： #自增（此列必须是：primary key或者unique key）
unique key： #唯一键，单独的唯一的
default： #默认值
unsigned： #非负数
comment： #注释
7.加上属性建表
#1.建表语句
create table students(
id int primary key auto_increment comment "学生id",
name varchar(12) not null comment "学生姓名",
age tinyint unsigned not null comment "学生年龄",
gender enum('M','F') default 'M' comment "学生性别",
cometime datetime default now() comment "入学时间");
#2.查看建表语句
mysql> show create table students;
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`name` varchar(12) NOT NULL COMMENT '学生姓名',
`age` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
`gender` enum('M','F') DEFAULT 'M' COMMENT '学生性别',
`cometime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
#3.插入数据
mysql> insert into students values(1,'qiudao',18,'M',now());
Query OK, 1 row affected (0.00 sec)
#因为主键相同无法插入
mysql> insert into students values(1,'qiudao',18,'M',now());
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
#应该
mysql> insert into students values('2','qiudao',18,'M',now());
Query OK, 1 row affected (0.00 sec)
#主键已经设置自增没必要自己插入
#正规插入数据的写法
mysql> insert students(name,age) values('lhd',18);
Query OK, 1 row affected (0.00 sec)
mysql> insert students(name,age) values('lhd',12);
Query OK, 1 row affected (0.01 sec)
mysql> select * from students;
+----+--------+-----+--------+---------------------+
| id | name | age | gender | cometime |
+----+--------+-----+--------+---------------------+
| 1 | qiudao | 18 | M | 2020-07-14 19:51:44 |
| 2 | qiudao | 18 | M | 2020-07-14 19:52:19 |
| 3 | lhd | 18 | M | 2020-07-14 19:53:50 |
| 4 | lhd | 12 | M | 2020-07-14 19:53:58 |
+----+--------+-----+--------+---------------------+
4 rows in set (0.00 sec)
3.删除表
drop table student;
4.修改表
#1.新建表
mysql> create table linux(daijiadong tinyint);
Query OK, 0 rows affected (0.04 sec
#2.修改表名
mysql> alter table linux rename linux9;
Query OK, 0 rows affected (0.01 sec)
#3.插入新字段
mysql> alter table linux9 add rengyufeng int;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| daijiadong | tinyint(4) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#4.插入多个新字段
mysql> alter table linux9 add liukong int,add wangzhangxing int;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| daijiadong | tinyint(4) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#5.插入字段到最前面
mysql> alter table linux9 add kangpeiwen varchar(100) first;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| daijiadong | tinyint(4) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
#6.插入字段到指定字段后面
mysql> alter table linux9 add chenjianqing varchar(100) after daijiadong;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| daijiadong | tinyint(4) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
#7.删除指定列
mysql> alter table linux9 drop daijiadong;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#8.修改字段
mysql> alter table linux9 change rengyufeng congtianqi int;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| congtianqi | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#9.修改字段属性
mysql> alter table linux9 modify congtianqi tinyint;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| congtianqi | tinyint(4) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
DCL:数据控制语言 （grant、revoke）
1.grant授权
# 授权语句
grant all on *.* to root@'172.16.1.%' identified by '123';
2.查看用户权限
mysql> show grants for root@'localhost';
| Grants for root@localhost |
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
#3.扩展参数
max_queries_per_hour：一个用户每小时可发出的查询数量
mysql> grant all on *.* to root@'172.16.1.%' identified by '123' with max_queries_per_hour 2;
Query OK, 0 rows affected (0.00 sec)
max_updates_per_hour：一个用户每小时可发出的更新数量
mysql> grant all on *.* to root@'172.16.1.%' identified by '123' with max_updates_per_hour 2;
Query OK, 0 rows affected (0.00 sec)
max_connections_per_hour：一个用户每小时可连接到服务器的次数
mysql> grant all on *.* to lhd@'172.16.1.%' identified by '123' with max_connections_per_hour 2;
Query OK, 0 rows affected (0.00 sec)
max_user_connections：允许同时连接数量
mysql> grant all on *.* to lhd@'172.16.1.%' identified by '123' with max_user_connections 1;
Query OK, 0 rows affected (0.00 sec)
2.revoke回收权限
mysql> revoke drop on *.* from lhd@'172.16.1.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for lhd@'172.16.1.%';
| Grants for lhd@172.16.1.%
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'lhd'@'172.16.1.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH MAX_CONNECTIONS_PER_HOUR 2 MAX_USER_CONNECTIONS 1
#所有权限
SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DROP, GRANT
3.授权超级管理员
grant all on *.* to root@'172.16.1.%' identified by '123' with grant option;