SQL语句

2019-08-19 本文已影响0人你好_请关照

SQL语句分类：

DDL 数据定义语言
DCL 数据控制语言
DML 数据操作语言

1、DDL 数据定义语言：

1.1库

1.1.1增(create)

建库的时候要注意修改库的字符集，使用charset 命令
这里注意utfmb4相较于utf8多了emoji 表情的字符类型

mysql> create database word charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| word               |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql>

1.1.2删库(drop)

mysql> drop database word ;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| world              |
+--------------------+
6 rows in set (0.00 sec)

mysql>

1.1.3查看库（show)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| word               |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> show create database word
    -> ;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| word     | CREATE DATABASE `word` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

1.1.4修改库（alter）

mysql> create database word;
Query OK, 1 row affected (0.00 sec)

mysql> show create database word;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| word     | CREATE DATABASE `word` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database word charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> show create database word;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| word     | CREATE DATABASE `word` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

1.2表

1.2.1 了解表结构，字段(列)和行构成了一张表

建表注意事项:
a、数据类型
b、约束
c、其他属性
d、字符集
e、存储引擎

1.2.2了解字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYTEXT	0-255字节	短文本字符串
TEXT	0-65 535字节	长文本数据
MEDIUMTEXT	0-16777215字节	中等长度文本数据
LONGTEXT	0-4 294967295字节	极大文本数据
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
BLOB	0-65 535字节	二进制形式的长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
LONGBLOB	0-4 294967295字节	二进制形式的极大文本数据
enum()		枚举类型(不能用作数字列)

1.2.3了解数字类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32768，32767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388608，8388607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2147483 648，2147483 647)	(0，4294 967295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38) 0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797693 1348623157 E+308，-2.2250738585072014 E-308)	0，(2.225 073 858 507 201 4 E-308，1.797 6931348623157E+308) 0，(2.225 073 858 507 201 4 E-308，1.7976931348623157E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

1.2.4了解时间类型

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155 YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP		1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999	1970-01-01 00:00:00.000000	unix时间戳

1.2.4常见的一些建表约束

功能	描述
primary key (PK)	标识该字段为该表的主键，可以唯一的标识记录，主键就是不为空且唯一当然其还有加速查询的作用
foreign key (FK)	标识该字段为该表的外键，用来建立表与表的关联关系
not null	标识该字段不能为空
unique key (UK)	标识该字段的值是唯一的
auto_increment	标识该字段的值自动增长（整数类型，而且为主键）
default	为该字段设置默认值

unsigned	将整型设置为无符号即正数
zerofill	不够使用0进行填充

1.2.5建表

建表语句规范
（1）表名必须是小写,不能数字开头,和业务有关
（2）每个表都要有主键列,可以定制无关列
（3）每个列要非空约束,可以定制默认值,数字列用0填充,字符串列null填充
（4）每个列必须要有注释.
（5）选择合适的数据类型
（6）表必须设置字符集和存储引擎

mysql> create table t1 (
    -> id int not null primary key auto_increment comment '学号',
    -> name varchar(10) not null comment '姓名',
    -> age tinyint not null default 99 comment '年龄',
    -> gender enum('n','f','m') not null default 'n' comment '性别',
    -> shijian timestamp  not null default now() comment '入学时间'
    -> )engine=innodb charset=utf8mb4 comment '学生表';
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+----------------+
| Tables_in_word |
+----------------+
| stu            |
| t1             |
+----------------+
2 rows in set (0.00 sec)

mysql>

1.2.6删除表

drop 表结构和数据一起删除
truncate 只删除数据不删表结构

mysql> drop table t1; #表结构和数据一起删除
Query OK, 0 rows affected (0.01 sec)
mysql> truncate table stu;  #只删除数据不删表结构
Query OK, 0 rows affected (0.48 sec)
mysql>

1.2.7查看表

mysql> desc stu;  #查看表结构
mysql> show tables;
mysql> show create table stu;

1.2.8修改表

mysql> alter table stu add telnum char(11) not null unique key comment '手机号'; #添加列
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table stu drop telnum; #删除列
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  alter table stu add telnum char(11) not null unique key comment '手机号' after age;  #在age列之后添加telnum列
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table stu add sid tinyint not null unique key first; #在首行添加sid列
Query OK, 0 rows affected (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table stu change gender xingbie char(1) not null default 'n' comment '性别'; #修改表属性
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table stu charset utf8mb4; #修改字符集
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table stu engine=innodb; #修改存储引擎
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>

2、DCL 数据控制语言

2.1 用户授权（grant）

SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, 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

ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能

作用对象	作用范围
.	所有库和表
wordpress.*	WordPress库下的所有表
wordpress.user	wordpress库下的user表

MySQL8.0版本之后就不能再授权的时候更改密码了，只能一步一步的来了
格式： grant 权限 on 作用对象(库或表) to 用户 identified by '123';

授权案例

2.1.1 需求1：开一个用户，允许wordpress用户，通过10网段登录管理wordpress库下的所有表，密码是123.

mysql> grant all on wordpress.* to wordpress@'10.0.0.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>

2.1.2 授权一个应用用户app，能够通过10网段，应用app下所有表，密码123。

mysql> grant select,insert,update on app.* to app@'10.0.0.%' identified by '123';

2.1.3 查看用户权限语句

mysql> show grants for root@'localhost'

2.2、收回用户的权限（revoke）

mysql>revoke drop,delete on *.* from xiaoxi@'10.0.0.%';

3、DML 数据操作语言(针对数据行)

3.1增(insert into )

mysql> insert into stu(id,NAME,age) values (1,'xiaoxi',19); #根据对应的列选择录入
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu values (2,'xiaoli',18,'m',now()); #根据表的默认顺序直接添加对应值录入即可
Query OK, 1 row affected (0.00 sec)

mysql> insert into stu values (3,'xiaoqing',18,'m',now()),(4,'xiaowang',20,'m',now())); #多行录入
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from stu;                                                           
+----+----------+-----+---------+---------------------+
| id | NAME     | age | xingbie | shijian             |
+----+----------+-----+---------+---------------------+
|  1 | xiaoxi   |  19 | n       | 2019-08-20 20:51:09 |
|  2 | xiaoli   |  18 | m       | 2019-08-20 20:54:50 |
|  3 | xiaoqing |  18 | m       | 2019-08-20 20:59:24 |
|  4 | xiaowang |  20 | m       | 2019-08-20 20:59:24 |
+----+----------+-----+---------+---------------------+
4 rows in set (0.00 sec)

3.2删(delete 逻辑删除)

mysql> delete from stu where  NAME='xiaoqing'; #delete 语句必须加where条件，否则删除所有表
Query OK, 1 row affected (0.02 sec)

mysql>

3.2.1 伪删除(通过添加状态列来解决)

mysql> select * from stu
    -> ;
+----+----------+-----+---------+---------------------+-------+
| id | NAME     | age | xingbie | shijian             | state |
+----+----------+-----+---------+---------------------+-------+
|  1 | xiaoxi   |  28 | n       | 2019-08-20 20:51:09 |     1 |
|  2 | xiaoli   |  18 | m       | 2019-08-20 20:54:50 |     1 |
|  4 | xiaowang |  20 | m       | 2019-08-20 20:59:24 |     1 |
+----+----------+-----+---------+---------------------+-------+
3 rows in set (0.00 sec)

mysql> update stu set state=0 where NAME='xiaoli';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stu where state=1
    -> ;
+----+----------+-----+---------+---------------------+-------+
| id | NAME     | age | xingbie | shijian             | state |
+----+----------+-----+---------+---------------------+-------+
|  1 | xiaoxi   |  28 | n       | 2019-08-20 20:51:09 |     1 |
|  4 | xiaowang |  20 | m       | 2019-08-20 20:59:24 |     1 |
+----+----------+-----+---------+---------------------+-------+
2 rows in set (0.00 sec)

mysql>

3.3改

mysql> update stu set age=28 where NAME='xiaoxi';#使用update语句时必须加where条件，否则更改所有列
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

4、数据库查询

4.1 select

4.1.1 select 单独查询使用

select @@使用：
select @@port;
select @@server_id;
select @@socket;

select 函数：
select now();
select database();
select 3+5;
select concat("hello");
select concat(user,"@",host) from mysql.user; #做拼接使用
select group_concat(user,"@",host) from mysql.user;

4.1.2 select 配合子句查询使用

select
form
where
group by
having 不走索引
order by
limit
union all

4.1.2.1 from子句应用

mysql> select * from stu;  #*表示查看表的所有列
+----+----------+-----+---------+---------------------+-------+
| id | NAME     | age | xingbie | shijian             | state |
+----+----------+-----+---------+---------------------+-------+
|  1 | xiaoxi   |  28 | n       | 2019-08-20 20:51:09 |     1 |
|  2 | xiaoli   |  18 | m       | 2019-08-20 20:54:50 |     0 |
|  4 | xiaowang |  20 | m       | 2019-08-20 20:59:24 |     1 |
+----+----------+-----+---------+---------------------+-------+
3 rows in set (0.00 sec)
mysql> select id,NAME,age,xingbie from stu; #查看指定的列 
+----+----------+-----+---------+
| id | NAME     | age | xingbie |
+----+----------+-----+---------+
|  1 | xiaoxi   |  28 | n       |
|  2 | xiaoli   |  18 | m       |
|  4 | xiaowang |  20 | m       |
+----+----------+-----+---------+
3 rows in set (0.00 sec)

4.1.2.2 where子句应用

<1>where 配合等值查询
查询中国所有的城市信息

mysql> select  * from city where countrycode='CHN';

<2>where 配合不等值查询
<、> <=、>=
查看世界少于100人的国家

mysql> select * from city where population<100;
+------+-----------+-------------+----------+------------+
| ID   | Name      | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 2912 | Adamstown | PCN         | –        |         42 |
+------+-----------+-------------+----------+------------+
1 row in set (0.01 sec)
mysql>

<3>where 配合like 模糊查询
如果使用like 进行模糊查询 %不能放在最前边，否则不走索引如: '%CH'这样是不对的

mysql> select * from city where countrycode like 'CH%';

<4>where 配合逻辑连接符号(and、or)
and 需要两个条件都成立才能返回查询结果
or 只需要一个条件成立，就可以返回查询结果

mysql> select * from city where countrycode='CHN' and population<5000000;

mysql> select * from city where population>1000000 and population <2000000; #查询查询1000000 到2000000 之间 


select * from city where countrycode='CHN' or countrycode='USA';

<5>where配合 between and、in的使用

mysql> select * from city where population between 1000000 and 2000000; #查询1000000 到2000000之间,等同于上面的and

mysql> select * from city where countrycode in ('CHN','USA'); #查询中国或美国的信息,等同于 or 语句。

4.1.2.3 group by 配合聚合函数应用--一般用于统计计算

常用的聚合函数

COUNT() --统计行数
SUM() --求和
MIX() --最小值
MAX() --最大值
AVG() --平均值
GROUP_CONCAT()

--需求：统计每个国家的城市个数
mysql> select countrycode,count(NAME) from city group by  countrycode;

上一条语句执行逻辑：
表结构如下（只是当前表的结构，不代表语句的执行结果）：
+------+-----------+-------------+--------------+------------+
| ID   | Name      | CountryCode | District     | Population |
+------+-----------+-------------+--------------+------------+
| 1890 | Shanghai  | CHN         | Shanghai     |    9696300 |
| 1891 | Peking    | CHN         | Peking       |    7472000 |
| 1892 | Chongqing | CHN         | Chongqing    |    6351600 |
| 1893 | Tianjin   | CHN         | Tianjin      |    5286800 |
| 1894 | Wuhan     | CHN         | Hubei        |    4344600 |
| 1895 | Harbin    | CHN         | Heilongjiang |    4289800 |
+------+-----------+-------------+--------------+------------+

第一步、 先执行 group by  countrycode -- 通过此语句对CountryCode做分组
第二步、count(NAME) --通过count()函数对各分组的NAME行数进行类加操作
第三步、select countrycode,count(NAME) -- 显示结果(因内容太多这里至截取部分)
+-------------+-------------+
| countrycode | count(NAME) |
+-------------+-------------+
| ABW         |           1 |
| AFG         |           4 |
| AGO         |           5 |
| AIA         |           2 |
| ALB         |           1 |
| AND         |           1 |
+-------------+-------------+

需求：统计一下世界上每个国家的城市名列表，截取前6行
mysql> select countrycode,count(id),group_concat(name)  from city group by countrycode limit 6;
+-------------+-----------+--------------------------------------+
| countrycode | count(id) | group_concat(name)                   |
+-------------+-----------+--------------------------------------+
| ABW         |         1 | Oranjestad                           |
| AFG         |         4 | Kabul,Qandahar,Herat,Mazar-e-Sharif  |
| AGO         |         5 | Luanda,Huambo,Lobito,Benguela,Namibe |
| AIA         |         2 | South Hill,The Valley                |
| ALB         |         1 | Tirana                               |
| AND         |         1 | Andorra la Vella                     |
+-------------+-----------+--------------------------------------+
6 rows in set (0.00 sec)

mysql>

需求：统计一下中国有多少个省
mysql> select countrycode,count(distinct district) from city  where countrycode='CHN' group by countrycode;
+-------------+--------------------------+
| countrycode | count(distinct district) |
+-------------+--------------------------+
| CHN         |                       31 |
+-------------+--------------------------+
1 row in set (0.00 sec)

4.1.2.4 having语句在group by 后作过滤使用。

mysql> select countrycode,count(distinct district) from city  
group by countrycode  
having countrycode='CHN';
+-------------+--------------------------+
| countrycode | count(distinct district) |
+-------------+--------------------------+
| CHN         |                       31 |
+-------------+--------------------------+
1 row in set (0.00 sec)

4.1.2.5 order by 对查询结果进行排序

mysql> select countrycode,count(distinct district) 
from city 
group by countrycode 
having count(distinct district)>20 
order by count(distinct district);
+-------------+--------------------------+
| countrycode | count(distinct district) |
+-------------+--------------------------+
| VNM         |                       21 |
| EGY         |                       22 |
| COL         |                       23 |
| UKR         |                       25 |
| IDN         |                       26 |
| IND         |                       26 |
| IRN         |                       27 |
| BRA         |                       27 |
| ROM         |                       29 |
| CHN         |                       31 |
| MEX         |                       33 |
| USA         |                       45 |
| JPN         |                       47 |
| TUR         |                       50 |
| RUS         |                       77 |
+-------------+--------------------------+
15 rows in set (0.01 sec)

mysql>

4.1.2.6limit 指定显示前几行，或者指定显示第几行。

mysql> select * from city where countrycode='CHN' order by district limit 6 #指定显示第六行
    -> ;
+------+------------+-------------+----------+------------+
| ID   | Name       | CountryCode | District | Population |
+------+------------+-------------+----------+------------+
| 1981 | Huaibei    | CHN         | Anhui    |     366549 |
| 1937 | Huainan    | CHN         | Anhui    |     700000 |
| 2005 | Ma´anshan  | CHN         | Anhui    |     305421 |
| 1964 | Wuhu       | CHN         | Anhui    |     425740 |
| 1916 | Hefei      | CHN         | Anhui    |    1369100 |
| 1961 | Bengbu     | CHN         | Anhui    |     449245 |
+------+------------+-------------+----------+------------+
6 rows in set (0.00 sec)

mysql> select * from city where countrycode='CHN' order by district limit 5,1; #跳过前5行，显示第六行
+------+--------+-------------+----------+------------+
| ID   | Name   | CountryCode | District | Population |
+------+--------+-------------+----------+------------+
| 1961 | Bengbu | CHN         | Anhui    |     449245 |
+------+--------+-------------+----------+------------+
1 row in set (0.00 sec)

mysql>

4.1.2.7 union all 连接两个语句用来去范围。

mysql> (select * from city where countrycode='CHN' order by district limit 5,1)
    -> union all 
    -> (select * from city where countrycode='CHN' order by district limit 6,1);
+------+--------+-------------+----------+------------+
| ID   | Name   | CountryCode | District | Population |
+------+--------+-------------+----------+------------+
| 1961 | Bengbu | CHN         | Anhui    |     449245 |
| 1961 | Bengbu | CHN         | Anhui    |     449245 |
+------+--------+-------------+----------+------------+
2 rows in set (0.36 sec)

mysql>

4.2 show 查询

常用show语句	释义
help show
show databases;	查看所有数据库
show tables;	查看当前库的所有表
SHOW TABLES FROM	查看某个指定库下的表
show create database world	查看建库语句
show create table world.city	查看建表语句
show grants for root@'localhost'	查看用户的权限信息
show charset；	查看字符集
show collation	查看校对规则
show processlist;	查看数据库连接情况
show index from	表的索引情况
show status	数据库状态查看
SHOW STATUS LIKE '%lock%';	查询数据库某些状态
SHOW VARIABLES	查看所有配置信息
SHOW variables LIKE '%lock%';	查看部分配置信息
show engines	查看支持的所有的存储引擎
show engine innodb status\G	查看InnoDB引擎相关的状态信息
show binary logs	列举所有的二进制日志
show master status	查看数据库的日志位置信息
show binlog evnets in	查看二进制日志事件
show slave status \G	查看从库状态
SHOW RELAYLOG EVENTS	从库relaylog事件信息
desc (show colums from city)	查看表的列定义信息