2019-04-23

作者: 点亮生活501 | 来源:发表于2019-04-23 16:27 被阅读0次

sql语句

创建库和设置字符集

CREATE DATABASE test;   
SHOW CREATE DATABASE test;  查询库
ALTER DATABASE test CHARSET utf8;  修改字符集

注意：修改字符集，修改后的字符集一定是原字符集的严格超级

表定义

创建表

CREATE TABLE stu(
id      INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname   VARCHAR(255) NOT NULL COMMENT '姓名',
sage    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' ,
sfz     CHAR(18) NOT NULL UNIQUE  COMMENT '身份证',
intime  TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';

image.png

#约束总结
1.非空约束   NOT NULL
2.唯一约束  UNIQUE
3.主键约束  PRIMARY KEY (自增 AUTO_INCREMENT)
4.默认约束  DEFAULT
5.检查约束   CHECK
6.外键约束   FOREIGN KEY

建表规范

1.表名小写
2.不能是数字开头
3.注意字符集和存储引擎
4.表名和业务相关
5.选择合适的数据类型
6.每个列都要有注释
7.每个列设置为非空，无法保证非空，用0来填充。

删除（生产中禁用命令）

drop table sch； 删表

在stu表中添加列信息

DESC stu;  查看列信息
ALTER TABLE stu ADD qq VARCHAR (20) NOT NULL UNIQUE COMMENT 'qq号'; 
ALTER TABLE stu ADD weixin VARCHAR (20) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;
ALTER TABLE stu ADD tel VARCHAR (20) NOT NULL UNIQUE  COMMENT '手机号' FIRST;
修改sname数据类型的属性
ALTER TABLE stu MODIFY sname VARCHAR(128)  NOT NULL ;
将sgender 改为 sg  数据类型改为 CHAR 类型
ALTER TABLE stu CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' ;
DESC stu;

4.3.4 表属性查询（DQL）

use school    切换库
show tables；显示库所有表
desc stu;   显示表所有列
show create table stu；
CREATE TABLE ceshi LIKE stu;

DCL 数据控制语言

grant    授权权限
revoke  回收权限

DML 数据操作语言
6.1 作用

对表中的数据行进行增、删、改

6.2 insert
--- 最标准的insert语句

INSERT INTO stu(id,sname,sage,sg,sfz,intime) 
VALUES
(1,'zs',18,'m','123456',NOW());
SELECT * FROM stu;
--- 省事的写法
INSERT INTO stu 
VALUES
(2,'ls',18,'m','1234567',NOW());
--- 针对性的录入数据
INSERT INTO stu(sname,sfz)
VALUES ('w5','34445788');
--- 同时录入多行数据
INSERT INTO stu(sname,sfz)
VALUES 
('w55','3444578d8'),
('m6','1212313'),
('aa','123213123123');
SELECT * FROM stu;

6.3 update

DESC stu;
SELECT * FROM stu;
UPDATE stu SET sname='zhao4' WHERE id=2;
注意：update语句必须要加where。

6.4 delete（危险！！）

DELETE FROM stu  WHERE id=3;

全表删除:

DELETE FROM stu
truncate table stu;

区别:
delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.
truncate: DDL操作,对与表段中的数据页进行清空,速度快.

伪删除：用update来替代delete，最终保证业务中查不到（select）即可

1.添加状态列
ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ;
SELECT * FROM stu;
2. UPDATE 替代 DELETE
UPDATE stu SET state=0 WHERE id=6;
3. 业务语句查询
SELECT * FROM stu WHERE state=1;

DQL应用(select )
7.1 单独使用

-- select @@xxx 查看系统参数
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;

-- select 函数();
SELECT NOW();
SELECT DATABASE();
SELECT USER();
SELECT CONCAT("hello world");
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg

7.2 单表子句-from
SELECT 列1,列2 FROM 表
SELECT * FROM 表

例子:
-- 查询stu中所有的数据(不要对大表进行操作)
SELECT * FROM stu ;

-- 查询stu表中,学生姓名和入学时间
SELECT sname , intime FROM stu;

=====================
oldguo带大家学单词：
world ===>世界
city ===>城市
country ===>国家
countrylanguage ===>国家语言

city:城市表
DESC city;
ID : 城市ID
NAME : 城市名
CountryCode: 国家代码，比如中国CHN 美国USA
District : 区域
Population : 人口

SHOW CREATE TABLE city;
SELECT * FROM city WHERE id<10;

======================
7.3 单表子句-where
SELECT col1,col2 FROM TABLE WHERE colN 条件;

7.3.1 where配合等值查询
例子:
-- 查询中国(CHN)所有城市信息

SELECT * FROM city WHERE countrycode='CHN';

-- 查询北京市的信息

SELECT * FROM city WHERE NAME='peking';

-- 查询甘肃省所有城市信息

SELECT * FROM city WHERE district='gansu';

7.3.2 where配合比较操作符(> < >= <= <>)
例子:
-- 查询世界上少于100人的城市

SELECT * FROM city WHERE population<100;

7.3.3 where配合逻辑运算符(and or )
例子:
-- 中国人口数量大于500w

SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;

-- 中国或美国城市信息

SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

7.3.4 where配合模糊查询
例子:
-- 查询省的名字前面带guang开头的
SELECT * FROM city WHERE district LIKE 'guang%';
注意:%不能放在前面,因为不走索引.

7.3.5 where配合in语句
-- 中国或美国城市信息

SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA');

7.3.6 where配合between and
例子:
-- 查询世界上人口数量大于100w小于200w的城市信息

SELECT * FROM city  WHERE population >1000000 AND population <2000000;
SELECT * FROM city  WHERE population BETWEEN 1000000 AND 2000000;

7.4 group by + 常用聚合函数

7.4.1 作用

根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

7.4.2 常用聚合函数

**max()**      ：最大值
**min()**      ：最小值
**avg()**      ：平均值
**sum()**      ：总和
**count()**    ：个数
group_concat() : 列转行

7.4.3 GROUP BY + 聚合函数公式

image.png

7.4.4 例子：

例子1：统计世界上每个国家的总人口数.

USE world
SELECT countrycode ,SUM(population)    FROM  city  GROUP BY countrycode;

例子2：统计中国各个省的总人口数量(练习)

SELECT district,SUM(Population) FROM city  WHERE countrycode='chn' GROUP BY district;

例子3：统计世界上每个国家的城市数量(练习)

SELECT countrycode,COUNT(id)  FROM city GROUP BY countrycode;

7.5 having

where|group|having

例子4：统计中国每个省的总人口数，只打印总人口数小于100

SELECT district,SUM(Population)
FROM city
WHERE countrycode='chn'
GROUP BY district
HAVING SUM(Population) < 1000000 ;

7.6 order by + limit

7.6.1 作用

实现先排序，by后添加条件列

7.5.2 应用案例

查看中国所有的城市，并按人口数进行排序(从大到小)

SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;

统计中国各个省的总人口数量，按照总人口从大到小排序

SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC ;

统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3 ;

LIMIT N ,M --->跳过N,显示一共M行
LIMIT 5,5

SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;

7.6 distinct：去重复

SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city  ;

7.7 联合查询- union all

-- 中国或美国城市信息

SELECT * FROM city 
WHERE countrycode IN ('CHN' ,'USA');

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION     去重复
UNION ALL 不去重复

7.7 join 多表连接查询

7.7.1 语法

7.5 order by + limit

7.5.1 作用

实现先排序，by后添加条件列

7.5.2 应用案例

1. 查看中国所有的城市，并按人口数进行排序(从大到小)
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;

 2. 统计中国各个省的总人口数量，按照总人口从大到小排序
SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC ;

3. 统计中国各个省的总人口数量，按照总人口从大到小排序，只取6-10

SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC
LIMIT 5 , 5 ;

或者：
SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC
LIMIT 5 OFFSET 5 ;

SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC
LIMIT 2 , 5 ;

SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC
LIMIT 10 ;

SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC
LIMIT 5 OFFSET 2 ;

7.6 distinct：去重复

SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city  ;

7.7 join 多表连接查询

7.7.0 案例准备

按需求创建一下表结构:

use school
student ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

-- 项目构建
drop database school;
CREATE DATABASE school CHARSET utf8;
USE school

CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL  COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');

INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');

INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

7.7.1 语法

image

查询张三的家庭住址

SELECT A.name,B.address FROM
A JOIN  B
ON A.id=B.id
WHERE A.name='zhangsan'

7.7.2 例子：

查询一下世界上人口数量小于100人的城市名和国家名

SELECT b.name ,a.name ,a.population
FROM  city  AS a
JOIN  country AS b
ON    b.code=a.countrycode
WHERE  a.Population<100

查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）

SELECT a.name,a.population,b.name ,b.SurfaceArea
FROM city  AS a JOIN country AS b
ON a.countrycode=b.code
WHERE a.name='shenyang';

7.7.3 别名

列别名,表别名
SELECT 
a.Name AS an ,
b.name AS bn ,
b.SurfaceArea AS bs,
a.Population AS bp
FROM city AS a  JOIN country AS b
ON a.CountryCode=b.Code
WHERE a.name ='shenyang';

7.7.4 多表SQL练习题

统计zhang3,学习了几门课

SELECT st.sname , COUNT(sc.cno)
FROM student AS st
JOIN
sc
ON st.sno=sc.sno
WHERE st.sname='zhang3'

查询zhang3,学习的课程名称有哪些?

SELECT st.sname , GROUP_CONCAT(co.cname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
WHERE st.sname='zhang3'

查询oldguo老师教的学生名.

SELECT te.tname ,GROUP_CONCAT(st.sname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
JOIN teacher AS te
ON co.tno=te.tno
WHERE te.tname='oldguo';

查询oldguo所教课程的平均分数

SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
WHERE te.tname='oldguo'

4.1 每位老师所教课程的平均分,并按平均分排序

SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
GROUP BY te.tname
ORDER BY AVG(sc.score) DESC ;

查询oldguo所教的不及格的学生姓名

SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course  AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE te.tname='oldguo' AND sc.score<60;

5.1 查询所有老师所教学生不及格的信息

SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course  AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE sc.score<60;

7.7.5 综合练习

1\. 查询平均成绩大于60分的同学的学号和平均成绩；
2\. 查询所有同学的学号、姓名、选课数、总成绩；
3\. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
4\. 统计各位老师,所教课程的及格率
5\. 查询每门课程被选修的学生数
6\. 查询出只选修了一门课程的全部学生的学号和姓名
7\. 查询选修课程门数超过1门的学生信息
8\. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
9\. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

8.information_schema.tables视图

DESC information_schema.TABLES
TABLE_SCHEMA    ---->库名
TABLE_NAME      ---->表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均行（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）

例子:

查询整个数据库中所有库和所对应的表信息

SELECT table_schema,GROUP_CONCAT(table_name)
FROM  information_schema.tables
GROUP BY table_schema;

统计所有库下的表个数

SELECT table_schema,COUNT(table_name)
FROM information_schema.TABLES
GROUP BY table_schema

查询所有innodb引擎的表及所在的库

SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`
WHERE ENGINE='innodb';

统计world数据库下每张表的磁盘空间占用

SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")  AS size_KB
FROM information_schema.tables WHERE TABLE_SCHEMA='world';

SELECT table_name,table_rows FROM information_schema.tables  WHERE table_schema='EASYTAXI' ORDER BY table_rows  DESC ;
查看EASYTAXI库每张表有多少条数据

统计所有数据库的总的磁盘空间占用

SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;
mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"

生成整个数据库下的所有表的单独备份语句

模板语句：
mysqldump -uroot -p123 world city >/tmp/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
FROM information_schema.tables
WHERE table_schema NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;

CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )

107张表，都需要执行以下2条语句

ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/dis.sql';

8.查询单个库的大小

select TABLE_SCHEMA ,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024/1024," MB") AS Total_KB FROM information_schema.tables WHERE TABLE_SCHEMA='EASYTAXI';

9.查看表分区

select 
partition_name part, 
partition_expression expr, 
partition_description descr, 
table_rows 
from information_schema.partitions where 
table_schema = schema() 
and table_name='TAXIMETER'; 
把下面的 TAXIMETER 更换成你要查道询的内分区表的容名字

9. 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                             #查看从库状态
desc  (show colums from city)               #查看表的列定义信息
http://dev.mysql.com/doc/refman/5.7/en/show.html

10 多表查询

内连接
隐式内连接
select 字段列表 from 表1，表2 where 条件 ... ;
显式内连接 
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ... ;

image.png

2019-04-23

sql语句

创建库和设置字符集

表定义

创建表

7.4 group by + 常用聚合函数

7.4.1 作用

7.4.2 常用聚合函数

7.4.3 GROUP BY + 聚合函数公式

7.4.4 例子：

7.5 having

7.6 order by + limit

7.6.1 作用

7.5.2 应用案例

7.6 distinct：去重复

7.7 联合查询- union all

7.7 join 多表连接查询

7.7.1 语法

7.5 order by + limit

7.5.1 作用

7.5.2 应用案例

7.6 distinct：去重复

7.7 join 多表连接查询

7.7.0 案例准备

7.7.1 语法

7.7.2 例子：

7.7.3 别名

7.7.4 多表SQL练习题

7.7.5 综合练习

8.information_schema.tables视图

9. show 命令 ***

10 多表查询

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读