一.什么是SQL?
SQL,英文全称为Structured Query Language,中文意思是结构化的查询语言，它是一种对关系型数据库中的数据进行定义和操作的语言。
结构化查询语言是一种数据库查询语言和程序设置语言，用于存取数据以及查询、更新、和管理关系数据库系统。
二.常用的SQL的类型
1.数据第一语言（DDL）
DDL全称为Data Definition Language，其语句包括动词CREATE、DROP和ALTER。可以使用改语言在数据库中创建新的库表和删除库表，或者为表添加字段、索引等。
针对库的DDL
建库规范：
1.库名不能有大写字母
2.建库要加字符集
3.库名不能有数字开头
4. 库名要和业务相关
#增库（创库）
mysql> create database db1 charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> create schema db2 charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
##DQL查看数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
##DQL查看建库语句（字符集）
mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
#修改字符（alter）
mysql> alter database db1 charset gbk;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db1;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
#删除数据库（drop）
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
针对表的 DDL
建表规范：
1. 表名小写
2. 不能是数字开头
3. 注意字符集和存储引擎
4. 表名和业务有关
5. 选择合适的数据类型
6. 每个列都要有注释
7. 每个列设置为非空，无法保证非空，用0来填充。
mysql> use db2
Database changed
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
`name` varchar(20) NOT NULL COMMENT '学生姓名',
`age` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
`gender` enum('f','m') DEFAULT NULL COMMENT '学生性别',
`cometime` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='入学时间' ;
#字段操作（alter）
1.默认添加字段
mysql> alter table student add score int;
2.在最前面添加字段
mysql> alter table student add class varchar(20) first;
3.将字段插入到某个字段的后面
mysql> alter table student add cno int after class;
4.修改字段的属性
mysql> alter table student modify cno tinyint;
5.修改字段的名称，也可以修改属性
mysql> alter table student change class classroom varchar(10);
6.删除字段
mysql> alter table student drop cno;
7.修改表明
mysql> alter table student rename stu;
2.数据操作语言（DML）
DML，全称为Data Processing Language,中文为数据操作语言。其语句的关键字为INSERT、UPDATA和DELETE。他们分别用于添加、修改、和删除表中的行（数据）。
#插入数据（insert）
mysql> insert into stu(id,name,age,gender,cometime) values(1,'唯爱熊',18,'m',now()),(2,'小雪',2'0,'f',now()),(3,'小雨',19,'f',now());
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from stu;
+----+-----------+-----+--------+---------------------+
| id | name | age | gender | cometime |
+----+-----------+-----+--------+---------------------+
| 1 | 唯爱熊 | 18 | m | 2019-12-03 16:08:31 |
| 2 | 小雪 | 20 | f | 2019-12-03 16:08:31 |
| 3 | 小雨 | 19 | f | 2019-12-03 16:08:31 |
+----+-----------+-----+--------+---------------------+
3 rows in set (0.00 sec)
#利用表数据局插入表数据
mysql> insert into student select * from stu;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-----------+-----+--------+---------------------+
| id | name | age | gender | cometime |
+----+-----------+-----+--------+---------------------+
| 1 | 唯爱熊 | 18 | m | 2019-12-03 16:08:31 |
| 2 | 小雪 | 20 | f | 2019-12-03 16:08:31 |
| 3 | 小雨 | 19 | f | 2019-12-03 16:08:31 |
+----+-----------+-----+--------+---------------------+
3 rows in set (0.00 sec)
#修改表数据 （update）
##规范操作
mysql> update stu set gender='f' where name='小雪';
##整列修改也需要带条件
mysql> update stu set age='100' where 1=1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from stu;
+----+-----------+-----+--------+---------------------+
| id | name | age | gender | cometime |
+----+-----------+-----+--------+---------------------+
| 1 | 唯爱熊 | 100 | m | 2019-12-03 16:08:31 |
| 2 | 小雪 | 100 | f | 2019-12-03 16:08:31 |
| 3 | 小雨 | 100 | f | 2019-12-03 16:08:31 |
+----+-----------+-----+--------+---------------------+
3 rows in set (0.00 sec)
#删除数据 （delete）
##1规范操作
mysql> delete from stu where id=1;
##2.规范操作删除所有
mysql> delete from stu where 1=1;
使用updata代替delete做伪删除
#1.添加状态列
mysql> alter table student add state enum('0','1') default '1';
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
#使用update删除数据
mysql> update student set state='0' where id='1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#查询数据
mysql> select * from student where state='1';
+----+--------+-----+--------+---------------------+-------+
| id | name | age | gender | cometime | state |
+----+--------+-----+--------+---------------------+-------+
| 2 | 小雪 | 20 | f | 2019-12-03 16:08:31 | 1 |
| 3 | 小雨 | 19 | f | 2019-12-03 16:08:31 | 1 |
+----+--------+-----+--------+---------------------+-------+
2 rows in set (0.00 sec)
3.数据控制语言（DCL）
DCL，全称为Data Control Language,其语句通过GRANT或REVOKE授权用户许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用REVOKE控制对表中的单个列的访问。
#grant
grant all on *.* to root1@'%' identified by '123' with grant option;
grant all privileges on *.* to root@'%' identified by '123' with grant option;
#其他参数（扩展）
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connections_per_hour：一个用户每小时可连接到服务器的次数
max_user_connections：允许同时连接数量
#给开发开权限(敏感信息脱敏时使用)
grant select() on ku.biao to dev1@'%' identified by '123' with max_queries_per_hour 1 max_updates_per_hour 1 max_connections_per_hour 1 max_user_connections 1;
#revoke
mysql> revoke select on *.* from root1@'%';
4.数据查询语言（DQL）
DQL，全称为Data Query Language,求语句也称为“数据检索语句”，作用是从表中获取数据，确定数据应怎样在应用程序中给出。关键字SELECT是DQL用最多的，其他DQL常用的保留字段有WHERE、ORDER BY、GROUP BY和HAVING。
1.单表查询
#建表
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`sex` enum('male','female') NOT NULL DEFAULT 'male',
`hire_date` date NOT NULL,
`post` varchar(50) NOT NULL,
`job_description` varchar(100) DEFAULT NULL,
`salary` double(15,2) NOT NULL,
`office` int(11) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
#导入数据
insert into employee(name,sex,hire_date,post,job_description,salary,office,dep_id) values
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('tianyun','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);
#查看表结构
mysql> desc employee;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | NO | | NULL | |
| job_description | varchar(100) | YES | | NULL | |
| salary | double(15,2) | NO | | NULL | |
| office | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+-----------------+-----------------------+------+-----+---------+----------------+
1）简单查询
#查看所有数据
mysql> select * from employee;
#去重查询DISTINCT
mysql> select post from employee;
mysql> select distinct post from employee;
#通过四则运算进行查询
mysql> select name,salary,salary*14 from employee;
mysql> select name,salary,salary*14 as annual_salary from employee;
mysql> select name,salary,salary*14 annual_salary from employee;
#自定义显示格式
mysql> select name as 名字,salary as 薪水 from employee;
+-----------+----------+
| 名字 | 薪水 |
+-----------+----------+
| jack | 5000.00 |
| tom | 5500.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| tianyun | 600.00 |
| harry | 6000.00 |
| emma | 20000.00 |
| christine | 2200.00 |
| zhuzhu | 2200.00 |
| gougou | 2200.00 |
+-----------+----------+
2）条件查询
a、语法
select * from 表名 where 条件
b、比较运算符
大于 小于 大于等于 小于等于 不等于
> < >= <= !=或<>
c、逻辑运算符
并且 或者 非
and or not
d、模糊查询
like
% 表示任意多个任意字符
_ 表示一个任意字符
e、范围查询
in 表示在一个非连续的范围内
between...and... 表示在一个连续的范围内
f、空判断
判断空：is null
判断非空：is not null
g、优先级
小括号,not 比较运算符， 逻辑运算符
and比or优先级高，如果同时出现并希望先选or，需要结合()来使用
单条件查询
mysql> select name,sex from employee where sex='male';
多条件查询
mysql> select name,sex from employee where sex='male' and salary > 5000;
关键字BETWEEN AND查询
mysql> select name,salary from employee where salary between 5000 and 10000;
关键字is null查询
mysql> select name,job_description from employee where job_description is null;
mysql> select name,job_description from employee where job_description is not null;
mysql> select name,job_description from employee where job_description='';
不连续范围查询in
mysql> select name,salary from employee where salary in (5000,7000.8000);
mysql> select name,salary from employee where salary not in (5000,7000.8000);
关键字like
#'%'
mysql> select * from employee where name like '%t%';
mysql> select * from employee where name like 't%';
mysql> select * from employee where name like '%t';
#'_'
mysql> select * from employee where name like 't__';
+----+------+------+------------+------------+-----------------+---------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+------+------+------------+------------+-----------------+---------+--------+--------+
| 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
+----+------+------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)
联合查询
mysql> select * from employee where name='tom' union all select * from employee where salary='5000';
3)分页查询limit
mysql> select * from employee limit 5;
mysql> select * from employee limit 0,5;
mysql> select * from employee limit 3,5;
4)联合函数查询
#函数:
max() 表示求此列的最大值
min() 表示求此列的最小值
sum() 表示求此列的和
avg() 表示求此列的平均数
count() 表示计算总行数，括号中可以写*或列名
distinct() 表示去重
mysql> select count(*) from employee;
mysql> select count(*) from employee where dep_id=101;
mysql> select max(salary) from employee;
mysql> select min(salary) from employee;
mysql> select avg(salary) from employee;
mysql> select sum(salary) from employee;
mysql> select sum(salary) from employee where dep_id=101;
5）分组查询 group by
mysql> select dep_id from employee group by dep_id;
6)函数结合group by一起使用
#此时此刻，我想吟诗一首
1.遇到统计想函数
2.形容词前group by
3.函数中央是名词
4.列名select后添加
#统计不同的办公地点的所有员工薪资总和；
mysql> select office,sum(salary) from employee group by office;
7)排序order by
#单列排序
mysql> select salary from employee order by salary; //默认为升序等价于添加asc
mysql> select salary from employee order by salary asc;
mysql> select salary from employee order by salary desc;
#多列排序
先按薪资排序，再按照办公地点排序
mysql> select salary,office from employee order by salary desc,office asc;
8）正则表达查询
mysql> select * from employee where name regexp '^t';
mysql> select * from employee where salary regexp '[5]+.*';
mysql> select * from employee where salary regexp '[5]{2}.*';
2.多表查询
这里提供一些数据直接导入数据库使用即可：链接：https://pan.baidu.com/s/12no-Gmd1CMfQBEUWNuHzHQ
提取码：uw8e
#导入数据到数据库
[root@db02 ~]# mysql <world.sql
1.传统连接
#世界上，小于100人的城市，在哪个省，属于哪个国家，人口数量是多少？
城市名 省名 国家名 人口数量
city.name city.district country.name city.population
select city.name as 城市名,city.district as 省,country.name as 国家,city.population as 城市人口数量
from city,country
where city.population < 100
and city.countrycode=country.code;
#世界上，小于100人的城市，在哪个省，属于哪个国家，人口数量是多少，说的是什么语言？
城市名 省名 国家名 人口数量 语言
city.name city.district country.name city.population countrylanguage.language
mysql> select city.name,city.district,country.name,city.population,countrylanguage.language
from city,country,countrylanguage
where city.population < 100
and city.countrycode=country.code
and country.code=countrylanguage.countrycode;
2.内连接
#世界上，小于100人的城市，在哪个省，属于哪个国家，人口数量是多少？
mysql>select city.name,city.district,country.name,city.population
from city join country
on city.countrycode=country.code
where city.population < 100;
#世界上，小于100人的城市，在哪个省，属于哪个国家，人口数量是多少，说的是什么语言？
A join B on 1 join C on 2 join D on 3
mysql> select city.name,city.district,country.name,city.population,countrylanguage.language
from city join country
on city.countrycode=country.code
join countrylanguage
on city.countrycode=countrylanguage.countrycode
where city.population < 100;
建议：小表在前，大表在后
3.自连接
#世界上小于100人的人口城市是哪个国家的？
city.countrycode=country.code
#世界上小于100人的城市说的什么语言？
城市名，语言，人口数量
city.name,countrylanguage.language,city.population
NATURAL JOIN
select city.name,countrylanguage.language,city.population
from city natural join countrylanguage
where city.population < 100;
#前提条件：两个表中必须有相同的列名字，并且数据一致
4.外连接
#左外连接
select city.name,city.countrycode,country.name
from city left join country
on city.countrycode=country.code
and city.population<100;
mysql> select city.name,city.countrycode,country.name from city left join country on city.countrycode=country.code and city.population<100 limit 10;
+----------------+-------------+------+
| name | countrycode | name |
+----------------+-------------+------+
| Kabul | AFG | NULL |
| Qandahar | AFG | NULL |
| Herat | AFG | NULL |
| Mazar-e-Sharif | AFG | NULL |
| Amsterdam | NLD | NULL |
| Rotterdam | NLD | NULL |
| Haag | NLD | NULL |
| Utrecht | NLD | NULL |
| Eindhoven | NLD | NULL |
| Tilburg | NLD | NULL |
+----------------+-------------+------+
#右外连接
mysql> select city.name,city.countrycode,country.name from city right join country on city.countrycode=country.code andd city.population<100 limit 10;
+------+-------------+----------------------+
| name | countrycode | name |
+------+-------------+----------------------+
| NULL | NULL | Aruba |
| NULL | NULL | Afghanistan |
| NULL | NULL | Angola |
| NULL | NULL | Anguilla |
| NULL | NULL | Albania |
| NULL | NULL | Andorra |
| NULL | NULL | Netherlands Antilles |
| NULL | NULL | United Arab Emirates |
| NULL | NULL | Argentina |
| NULL | NULL | Armenia |
+------+-------------+----------------------+
mysql> select city.name,country.code,country.name from city right join country on city.countrycode=country.code and city.population<100 limit 10;
+------+------+----------------------+
| name | code | name |
+------+------+----------------------+
| NULL | ABW | Aruba |
| NULL | AFG | Afghanistan |
| NULL | AGO | Angola |
| NULL | AIA | Anguilla |
| NULL | ALB | Albania |
| NULL | AND | Andorra |
| NULL | ANT | Netherlands Antilles |
| NULL | ARE | United Arab Emirates |
| NULL | ARG | Argentina |
| NULL | ARM | Armenia |
+------+------+----------------------+
网友评论