linux系统mysql普通语句
SQL语句
1.语义
1.DDL 数据定义语言
2.DCL 数据控制语言
3.DML 数据操作语言
4.DQL 数据查询语言
一、DDL数据定义语言（create、drop）
1.create对库操作
#1.完整的建库语句
mysql> create database db5 character set=utf8 collate=utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
#2.常用的建库语句
mysql> create database db6 charset utf8;
Query OK, 1 row affected (0.00 sec)
2.create对表的操作
#1.简单的建表语句
mysql> create table tb1 (id int);
Query OK, 0 rows affected (0.08 sec)
#2.数据类型
int 整数 -2^31 - 2^31-1 (-2147483648 - 2147483647)
tinyint 最小整数 -128 - 127 #年龄 0 - 255
varchar 字符类型（变长） #身份证
char 字符类型（定长）
enum 枚举类型 #给它固定选项，只能选则选择项中的值 性别
datetime 时间类型 年月日时分秒
#3.建表语句
mysql> create table tb2 (id int,name varchar(12),age tinyint,sex enum('m','f'),cometime datetime);
Query OK, 0 rows affected (0.02 sec)
#4.数据属性
not null： #非空
primary key： #主键（唯一且非空的）
auto_increment： #自增（此列必须是：primary key或者unique key）
unique key： #唯一键，单独的唯一的
default： #默认值
unsigned： #非负数
comment： #注释
#5.结合数据属性建表
create table student (
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 'f' comment "学生性别",
cometime datetime default now() comment "入学时间");
#6.修改表
mysql> alter table student add birthday datetime COMMENT '学生生日';
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
二、DCL数据控制语言（grant、revoke）
1.grant授权命令
#1.授权命令（没有grant权限）
mysql> grant all on *.* to root@'172.16.1.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
#2.全库全表授权
mysql> grant all on *.* to root@'172.16.1.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
#3.单库授权
mysql> grant all on mysql.* to root@'172.16.1.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
#4.单表授权
mysql> grant all on mysql.user to root@'172.16.1.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
#5.单列授权（脱敏）
mysql> grant select(user,host) on mysql.user to root@'172.16.1.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
#6.授权一个超级管理员
grant all on *.* to root@'172.16.1.%' identified by '123' with grant option;
Query OK, 0 rows affected (0.00 sec)
2.revoke回收权限
#1.回收权限
mysql> revoke drop,delete on *.* from root@'172.16.1.%';
Query OK, 0 rows affected (0.00 sec)
#2.查看用户权限
mysql> show grants for root@'172.16.1.%';
三、DML数据操作语言（insert、delete、update）
1.insert 命令
1）查看表结构
mysql> desc student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(12) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| gender | enum('m','f') | YES | | f | |
| cometime | datetime | YES | | CURRENT_TIMESTAMP | |
| birthday | datetime | YES | | NULL | |
+----------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)
2）插入数据（不规范写法）
mysql> insert into student values(1,'邱导',78,'f',now(),'1942-07-14');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 78 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
1 row in set (0.00 sec)
3）插入数据（规范写法）
#1.插入指定列数据
mysql> insert into student(name,age) values('曾导','84');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 78 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 78 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
+----+--------+-----+--------+---------------------+---------------------+
3 rows in set (0.00 sec)
#2.插入指定列数据
mysql> insert into student(name,age,birthday) values('曾导','84','1936-02-20');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 78 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 78 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
4 rows in set (0.00 sec)
4）插入多条数据
mysql> insert into student(name,age,birthday) values('好大','18',1936-02-21),('好小','28','1992-01-01');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 78 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 78 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 |
| 8 | 好小 | 28 | f | 2020-07-15 09:31:07 | 1992-01-01 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
8 rows in set (0.00 sec)
2.update命令
1）查看数据
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 78 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 78 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 |
| 8 | 好小 | 28 | f | 2020-07-15 09:31:07 | 1992-01-01 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
8 rows in set (0.00 sec)
2）修改数据
#使用update语句必须要加where条件
mysql> update student set age=18 where name='邱导';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 18 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 18 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 |
| 8 | 好小 | 28 | f | 2020-07-15 09:31:07 | 1992-01-01 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
8 rows in set (0.00 sec)
3）指点修改一条数据
#如果数据库有主键，一定使用主键
mysql> update student set age=88 where name='邱导' and cometime='2020-07-15 09:21:12';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update student set age=88 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 88 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 88 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 |
| 8 | 好小 | 28 | f | 2020-07-15 09:31:07 | 1992-01-01 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
8 rows in set (0.00 sec)
3.delete语句
1）删除数据
#1.先查看数据，确认要删除的数据，怎么确定唯一
#2.使用delete语句也一定要加where条件
mysql> delete from student where id=8;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 88 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 88 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
7 rows in set (0.00 sec)
#3.如果就是要清空表
mysql> delete from student where 1=1;
Query OK, 1 row affected (0.01 sec)
#(危险)
truncate table student;
drop table student
4.使用update代替delete
1）添加状态字段
mysql> alter table student add status enum('1','0') default 1;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+--------+
| id | name | age | gender | cometime | birthday | status |
+----+--------+-----+--------+---------------------+---------------------+--------+
| 1 | 邱导 | 88 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 | 1 |
| 2 | 邱导 | 88 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 | 1 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL | 1 |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 | 1 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 | 1 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 | 1 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 | 1 |
+----+--------+-----+--------+---------------------+---------------------+--------+
7 rows in set (0.00 sec)
2）使用update代替delete
#相当于删除学生
mysql> update student set status='0' where id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+--------+
| id | name | age | gender | cometime | birthday | status |
+----+--------+-----+--------+---------------------+---------------------+--------+
| 2 | 邱导 | 88 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 | 0 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL | 1 |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 | 1 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 | 1 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 | 1 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 | 1 |
+----+--------+-----+--------+---------------------+---------------------+--------+
6 rows in set (0.00 sec)
#相当于学生回来
mysql> update student set status='1' where id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+--------+
| id | name | age | gender | cometime | birthday | status |
+----+--------+-----+--------+---------------------+---------------------+--------+
| 2 | 邱导 | 88 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 | 1 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL | 1 |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 | 1 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 | 1 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 | 1 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 | 1 |
+----+--------+-----+--------+---------------------+---------------------+--------+
6 rows in set (0.00 sec)
四、DQL数据查询语言（select、desc）
1.select查询语句
1）查询表中所有的数据
#很危险，数据量过大，容易导致down机
mysql> select * from student;
#先查询数据总量，然后决定是否可以查询所有数据
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.01 sec)
2）查看指定列的数据
mysql> select user,host from mysql.user;
+--------+------------+
| user | host |
+--------+------------+
| root | % |
| root | 127.0.0.1 |
| lhd | 172.16.1.% |
| qiudao | 172.16.1.% |
| root | 172.16.1.% |
| root | ::1 |
| | db03 |
| root | db03 |
| | localhost |
| root | localhost |
+--------+------------+
10 rows in set (0.01 sec)
3）按条件查询
mysql> select name,gender from student where name='邱导';
+--------+--------+
| name | gender |
+--------+--------+
| 邱导 | f |
+--------+--------+
1 row in set (0.00 sec)
2.简单查询练习
1）将sql文件导入数据
#方式一：
[root@db03 ~]# mysql -uroot -p123 < world.sql
#方式二:
mysql> source /root/world.sql;
#方式三:
mysql> \. /root/world.sql;
2）查看数据
mysql> use world;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.00 sec)
mysql> select * from city;
3）查询练习
#1.查看表结构
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
#2.查看所有数据
mysql> select * from city;
#3.查看指定列的数据
mysql> select Name,Population from city;
#4.查看数据时排序（按照人口数量）
#升序
mysql> select Name,Population from city order by Population;
#降序
mysql> select Name,Population from city order by Population desc;
#5.查询部分数据
#查看前十条数据
mysql> select Name,Population from city order by Population desc limit 10;
#6.按照步长查询数据
mysql> select id,Name,Population from city limit 50,50;
#50起始位置 50步长
3.条件查询
#1.条件查询就是使用where语句，where语句可以使用的符号
条件符号：= < > <= >= != <> or and like
精确匹配：=
范围匹配：< > <= >= != <>
模糊匹配：like
连接语句：or and
#2.查询中国的城市人口
mysql> select name,population from city where CountryCode='CHN';
#3.查询黑龙江人口数量
mysql> select name,population from city where countrycode='CHN' and District='heilongjiang';
#4.查询中国人口数量小于100000的城市
mysql> select name,population from city where countrycode='CHN' and population < 100000;
#5.模糊匹配
#匹配以N结尾的数据
mysql> select name,countrycode from city where countrycode like '%N';
#匹配以N开头的数据
mysql> select name,countrycode from city where countrycode like 'N%';
#匹配包含N的数据
mysql> select name,countrycode from city where countrycode like '%N%';
#6.查询中国或美国的人口数量
#使用or
mysql> select name,population from city where countrycode = 'CHN' or countrycode = 'USA';
#使用in
mysql> select name,population from city where countrycode in ('CHN','USA');
#使用union all
mysql> select name,population from city where countrycode = 'CHN' union all select name,population from city where countrycode = 'USA';
五、select高级用法
#多表联查，联表查询
1.传统连接
1）集合
#集合
[qiudao,zengdao,qiandao]
[80,90,100]
#数据库
id:[1,2,3]
name:[qiudao,zengdao,qiandao]
id:[1,2,3]
mark:[80,90,100]
2）建表
mysql> create table students(id int,name varchar(10));
Query OK, 0 rows affected (0.08 sec)
mysql> create table score(id int,mark int);
Query OK, 0 rows affected (0.05 sec)
3）插入数据
mysql> insert into students values(1,'qiudao'),(2,'qiandao'),(3,'zengdao');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into score values(1,80),(2,90),(3,100);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
4）数据查询
#查看两个表的数据
mysql> select * from students;
+------+---------+
| id | name |
+------+---------+
| 1 | qiudao |
| 2 | qiandao |
| 3 | zengdao |
+------+---------+
3 rows in set (0.00 sec)
mysql> select * from score;
+------+------+
| id | mark |
+------+------+
| 1 | 80 |
| 2 | 90 |
| 3 | 100 |
+------+------+
3 rows in set (0.00 sec)
#查看邱导的分数
mysql> select students.name,score.mark from students,score where students.id=1 and score.id=1;
mysql> select students.name,score.mark from students,score where students.id=score.id and name='qiudao';
+--------+------+
| name | mark |
+--------+------+
| qiudao | 80 |
+--------+------+
1 row in set (0.01 sec)
#查询所有学生成绩
mysql> select students.name,score.mark from students,score where students.id=score.id
5）练习题一：
连表查询：世界上小于100人的城市在哪个国家？请列出城市名字，国家名字与人口数量
#1.确认我要查哪些内容
国家名字 城市名字 城市人口数量 小于100人
#2.确认在哪个表
country.name city.name city.population
#3.找出两个表相关联的字段
city.countrycode country.code
#4.编写语句
mysql> select country.name,city.name,city.population from country,city where city.countrycode=country.code and city.population < 100;
+----------+-----------+------------+
| name | name | population |
+----------+-----------+------------+
| Pitcairn | Adamstown | 42 |
+----------+-----------+------------+
1 row in set (0.01 sec)
6）练习题二：
连表查询：世界上小于100人的城市在哪个国家，是用什么语言？请列出城市名字，国家名字与人口数量和国家语言
#1.确认我要查哪些内容
国家名字 城市名字 城市人口数量 国家使用的语言 小于100人
#2.确认在哪个表
country.name city.name city.population countrylanguage.language
#3.找出三个表相关联的字段
country.code city.countrycode countrylanguage.countrycode
#4.写sql语句
mysql> select country.name,city.name,city.population,countrylanguage.language from country,city,countrylanguage where country.code=city.countrycode and city.countrycode=countrylanguage.countrycode and city.population < 100;
+----------+-----------+------------+-------------+
| name | name | population | language |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown | 42 | Pitcairnese |
+----------+-----------+------------+-------------+
1 row in set (0.04 sec)
2.自连接
#自己查找相同字段，使用自连接，两个关联的表必须有相同字段和相同数据
SELECT city.name,city.countrycode,countrylanguage.language,city.population
FROM city NATURAL JOIN countrylanguage
WHERE population > 1000000
ORDER BY population;
#两个表中没有相同字段不行，字段相同值不同不行
SELECT country.name,city.name,city.population FROM city NATURAL JOIN country WHERE population < 100;
#注意：
1.自连接必须有相同字段和相同值
2.两个表中的数据必须完全相同
