SQL语句入门
目录MySQL的连接管理
自带的连接工具（客户端）
- mysql
# mysql客户端选项：
-u：user 指定MySQL的用户
-p：password 指定MySQL用户的密码
-S：socket 指定socket文件的位置
-h：host 指定主机IP地址
-P：port 指定端口
-e：exec 在数据库外直接执行SQL语句
例：
[root@db01 ~]# mysql -uroot -pabc -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
--protocol=name：指定连接方式（基本不用 因为本来加IP地址就是TCP 不加默认就是socket连接）
- mysqladmin
- mysqldump（逻辑备份）
第三方连接工具（客户端）
- Navicat
- SQLmanager
- SQLyog
MySQL启动关闭流程
# 1，mysql.server (启动脚本)
mysqld.service (启动脚本) 脚本中指定了配置文件位置 --defaults-file=/etc/my.cnf
||
# 2，调用MySQL的二进制程序：mysqld_safe
||
# 3，启动起来mysqld这个守护进程
启动命令
# 启动方式一：直接绝对路径启动
/etc/init.d/mysqld start
# 启动方式二：加入systemd来管理
systemctl start mysqld
# 启动方式三：mysqld_safe的方式
mysqld_safe --选项
关闭命令
# 关闭方式一：直接绝对路径关闭
/etc/init.d/mysqld stop
# 关闭方式二：加入systemd来管理
systemctl stop mysqld
# 关闭方式三：mysqladmin方式关闭 要加上数据库用户密码
mysqladmin -uroot -p shutdown
# 想要关闭多实例的方法 直接-S指定要关闭实例的sock文件即可
mysqladmin -uroot -p -S /data/3309/data/3309.sock shutdown
# 关闭方式四：kill的方式 但不是实在没办法了 不要使用kill 会导致数据丢失 数据库宕机
pkill mysqld ? # 实在要用 pkill相对好一些
kill -9 pid ?
killall mysqld ?
MySQL实例初始化配置
- 预编译（源码安装编译时指定的配置文件路径，cosk文件路径）
- 命令行（直接用选项指定配置文件位置，cosk文件位置）
- 配置文件（启动之前需要读取配置文件）
- /etc/my.cnf
- /etc/mysql/my.cnf
- $basedir/my.cnf
- --defaults-extra-file=/opt/my.cnf（指定额外的配置文件）
- ~/.my.cnf
优先级如下（读取顺序是反的 后读取的配置文件会覆盖前面的配置文件 但不相同的配置会执行 不会被覆盖）
~/.my.cnf > --defaults-extra-file=/opt/my.cnf > $basedir/my.cnf > /etc/mysql/my.cnf > /etc/my.cnf
注意：如果启动MySQL加了--defaults-file=/etc/my.cnf选项，就只会读取指定的配置文件
优先级练习
## 用以下四中方式指定sock文件 请写出它们的优先级
# 方式一：cmake编译时就直接指定sock文件位置
socket=/application/mysql/tmp/mysql.sock
# 方式二：命令行--socket选项直接指定sock文件位置
mysqld --socket=/tmp/mysql.sock
# 方式三：命令行--default选项指定配置文件位置
mysqld --defaults-file=/tmp/a.txt配置文件中[mysqld]标签下：socket=/tmp/test.sock
# 方式四：配置文件里指定sock文件位置
/etc/my.cnf中[mysqld]标签下：socket=/opt/mysql.sock
## 结论
默认配置，优先级
1.命令行
2.配置文件
- ~/.my.cnf
- --defaults-extra-file=/opt/my.cnf
- $basedir/my.cnf
- /etc/mysql/my.cnf
- /etc/my.cnf
3.编译参数
初始化配置的作用
- 作用一：影响实例的启动（mysqld）
- 作用二：影响到客户端的连接
## 服务端标签：这两个标签下的配置，都是来影响服务端启动的
[mysqld] = [server]
## 客户端标签：
# 这几个标签下的配置，影响对应的客户端命令
[mysql] [mysqladmin] [mysqldump]
# 这个标签下的配置，影响所有的客户端命令
[client]
### 注意：修改客户端配置，不需要重启mysql，修改服务端配置[mysqld]，[server]下的配置需要重启mysql
## 如下 直接在客户端标签下加上用户和密码 就能免密登陆数据库
[mysqld]
skip_name_resolve
basedir=/application/mysql
datadir=/application/mysql/data
server_id=10
socket=/opt/mysql.sock
[client]
user=root
password=abc
socket=/opt/mysql.sock
字符集设置
操作系统设置字符集
## CentOS6
[root@db01 ~]# source /etc/sysconfig/i18n
[root@db01 ~]# echo $LANG
zh_CN.UTF-8
## CentOS7
[root@db01 ~]# vim /etc/locale.conf
LANG="en_US.UTF-8"
[root@db01 ~]# echo $LANG
en_US.UTF-8
## 中国windows系统的默认字符集
GBK 国标扩
连接工具设置字符集
Xshell
数据库配置设置字符集
## 修改以创建库的字符集方法
# 先查看所有库的字符集
root@localhost:(none)>show variables like '%char%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_results | utf8 |
| character_set_server | ladin1 |
+--------------------------+-------------------------------------------+
# 将不是utf8的库名复制添加到mysql配置文件的 [mysqld] 下指定utf8 然后重启mysql
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
character_set_server=utf8
[root@db01 ~]# systemctl restart mysqld
建库建表级别设置字符集
## 建库设置字符集
create database zls111 charset utf8 collate utf8_general_ci;
## 建表设置字符集
create table test(
id int,
name varchar(10)
) default charset utf8;
MySQL的SQL语句
客户端命令
- mysql
## 查看命令帮助
- ?
- \?
- help
- \h
## 查看状态
- status
- \s
## 退出
- exit
- quit
- \q
## 结束当前的SQL语句
- \c
- Ctrl + c
- MySQL5.6中：直接退出MySQL
- MySQL5.7中：结束当前SQL语句，类似于\c
## 在MySQL中执行系统命令
- \!
- system 例：system ls -l
## 临时将操作内容和结果记录到指定的文件中（退出数据库结束）
- tee 例：tee /tmp/mysql.log
- \T 例：\T /tmp/mysql.log
## 切换数据库
- use 例：use mysql
- \u 例：\u mysql
## 导入sql文件中的数据（指定要导入的数据的位置）
- source 例：source /tmp/sys.sql
- \. 例：\. /tmp/zls.sql
## 输出内容格式化（key:value）方式，显示数据
- \G 例：mysql> select * from mysql.user\G
### 客户端配置，加入以下内容 显示当前所在数据库及登录用户
[mysql]
prompt="\u@\h:\d>"
- mysqladmin
## 数据库外命令行执行（有密码要指定 -u用户名 和 -p密码）
# 修改密码或者设置密码
mysqladmin password '密码'
# 关闭mysql服务
mysqladmin shutdown
# 检测MySQL是否存活
mysqladmin ping
# 查看MySQL的状态
mysqladmin status
# 查看MySQL的默认配置（内置变量）（可以直接把查出来的变量名拿来写到配置文件中修改值）
mysqladmin variables
# 在库外创建数据库
mysqladmin create 库名
# 在库外删除数据库
mysqladmin drop 库名
# 重新加载数据库（这是重新加载数据库里面的内容 和结束临时生效的内容 不是直接重启数据库）
mysqladmin reload
# 刷新授权表
mysqladmin flush-log
SQL层的SQL语句
什么是SQL语句
结构化的查询语句
标准：SQL-92
SQL语句的分类
DDL
### DDL含义：
Database Definition Language
数据 定义 语言
### 开发规范：
(01) 表名不能大写,数字开头,16个字符串
(02) 表名和业务有关
(03) drop 语句禁止
(04) 选择合适的数据类型
(05) 必须要有主键
(06) 列尽量非空约束
(07) 减少外键约束
(08) 必须设置存储引擎和字符集
(09) 列必须要有注释
(10) 对于非负数设置数据类型约束--无符号
### 对于库的操作
## 增
# 语法：
root@localhost:(none)>help create database
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
}
# 创建库方法
create database 库名;
create schema 库名;
# 创建库时指定字符集
create database 库名 character set utf8;
create database 库名 charset utf8;
# if not exists ：判断要创建的库存不存在 不存在就创建
create database if not exists 库名 charset utf8;
# collate ：指定校验规则 比如_bin结尾的就是大小写不敏感 _ci就是大小写敏感
create database if not exists 库名 charset utf8 collate utf8_bin;
## 删
drop database 库名;
## 改
# 修改字符集
alter database wyk1 charset latin1;
#### DQL查看数据库的字符集
show create database wyk1;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| wyk1 | CREATE DATABASE `wyk1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
### 对于表的操作
# 增
## 建表语法：
create table 表名(字段1 数据类型 约束,字段 数据类型 约束....)
# 建表，至少要给的是 表名 字段名称 数据类型
## 要么先切到要建表的库 要么在要建的表名前加库名 例：
root@localhost:(none)>create table wyk.student(id int not null primary key auto_increment,name varchar(10),age tinyint,gender enum('0','1'));
# 可以写成以下格式 方便区分每条字段
root@localhost:(none)>create table wyk.student2(
-> id int,
-> name varchar(10),
-> age tinyint,
-> gender enum('0','1'));
## 数据类型
- int： 整数 范围：-2^31 ~ 2^31 -1
- zerofill：零填充 当int(3) 指定位数后 还是可以写入超过指定位数的的内容
所以约束的不是整型的位数 而是加零填充后 会用0填充到指定位数
- varchar：字符类型 （变长：字符串内容有几个字符 都算占了指定的个数里的几个字符）
- char： 字符类型 （定长：不管字符串内容有几个字符 都算占了指定的个数的字符数）
例：
name varchar(10)
wyk # 占了3个
huanglong # 占了9个
name char(10)
wyk # 占了10个
huanglong # 占了10个
- bigunt： 较大整数 范围 2^63 ~ 2^63-1
- tinyint： 较小整数 -128 ~ 127（一共255个数 如果指定了非负 那么这个选项的范围就是0~255）
- enum： 枚举类型（指定选项 比如 男，女 那输入的内容只能是 男 或者 女）
例：
enum('A','B','C','D')
- datetime： 时间类型 年月日时分秒
- float：单精度浮点数 语法：float (指定数字的位数 不包括小数点)
- double：双精度浮点数 语法：double (指定小数点前面的数的位数,指定小数点后面数的位数)
- bit：位字段值
## 约束
- not null： 非空
- null：可以为空
- primary key： 主键（唯一且非空的）一张表只能有一个主键
- auto_increment： 自增（此列必须是：primary key主键 或者unique key唯一键）
- unique key： 唯一键，输入的内容是唯一的 不能重复 unique key + not null 相当于主键
- default： 设置默认值（比如性别 默认 男）
- unsigned： 无符号（非负）(必须写在数据类型后面，用来约束数据类型)
- comment： 注释该字段
# 案例：学生表格
root@localhost:(none)>create table stu(
-> id int primary key auto_increment comment '学生学号',
-> name varchar(10) not null comment '学生姓名',
-> age tinyint unsigned not null default 18 comment '学生年龄',
-> gender enum('f','m') not null default 'f' comment '学生性别',
-> date datetime not null default NOW() comment '入学时间',
-> phone char(11) not null unique key comment '学生手机号',
-> bir datetime not null comment '学生生日'
-> );
root@localhost:(none)>desc wyk.stu;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 18 | |
| gender | enum('f','m') | NO | | f | |
| date | datetime | NO | | CURRENT_TIMESTAMP | |
| phone | char(11) | NO | UNI | NULL | |
| bir | datetime | NO | | NULL | |
+--------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)
## 删
# 删表
drop table wyk.wyk.stu;
# 删用户
drop user 用户名@'对应的子域'
drop user wyk@'%';
## 改
# 修改表名语法：
alter table 表名 rename 新表名;
alter table stu rename stu2;
# 添加字段（将字段添加在表的最后一列）
alter table stu add aoxin varchar(10) not null;
alter table stu2 add aoxin varchar(10) not null;
# 带默认值添加字段
alter table stu3 add wjh char(11) default 'sb';
# 添加多个字段（每个字段都要加add）
alter table stu2 add jherz varchar(10) not null,add jhhaoerz char(10);
# 按指定位置添加字段
alter table stu2 add sbsg char(1) after jhhaoerz;
# 将字段添加在最前面一列（first）
alter table stu2 add wykyyds char(1) first;
# 删除字段（drop）
alter table stu2 drop jhhaoerz;
# 修改数据类型和属性（modify）
alter table stu2 modify gender enum('a','b') default 'a';
# 修改字段名 数据类型 属性（change）
alter table 表名 change 字段原名 要改的名 要改的属性;
alter table stu2 change jherz sbaoxin varchar(10);
DML
### DML含义：所有的DML都是操作表内容的
Data Manipulation Language
数据 操作 语言
## 增
# 插入单条数据
insert into 表名(字段1,字段2,字段3) value(写入的数据1,写入的数据2,写入的数据3);
# 字段可以换顺序 那么后面写入的内容也要对应顺序 NOW()是mysql内置变量是当前的时间
insert into stu(name,gender,age,date,phone,bir,id) value('wyk','f',255,'2022-08-10','133',NOW(),1);
# 插入多条数据
root@localhost:(none)>insert into stu(name,gender,age,date,phone,bir,id)
-> value('wyk1','f',255,'2022-08-10','136',NOW(),4),
-> ('wyk2','m',38,'2022-09-10','137',NOW(),5);
# 有默认值的字段可以不加 那后面写入的内容就写前面对应的字段就行
insert into stu(name,bir,phone) value('wyk4',NOW(),'138');
# 不规范写法：这种写法只能按表里字段的顺序将对应的数据一个个写入 每个字段都要写 不管有没有默认值
insert stu value(7,'wyk5',18,'m',NOW(),'139',NOW());
## 删
# 危险写法：直接delete删 这样会把这个表里所有的数据都删完
delete from stu2;
# 要养成习惯 使用delete先在后面加上where（条件） 指定要删的内容
delete from stu2 where name='wyk1';
# 想把数据全部删除也要加where 写一个永远成立的条件即可
delete from stu where 10=10;
# 注意：一般在删除数据时，我们会根据唯一性（主键，唯一键）的字段，进行删除
delete from stu2 where id='1';
# 删除多个字段内容重复的数据方法： （ and连接多个条件 ）
delete from mysql.user where user='wyk' and host='%';
## 改
# update 使用update一定要加条件 不然会把指定字段的所有内容就修改
update stu2 set id=3 # 错误写法
update stu2 set id=3 where name='wyk'; #正确写法 加条件
# 使用update替代delete删除数据（企业中）
1.给表中，加一个字段 它是一个状态列 判断存不存在 0是不存在 1是存在
alter table stu=2 add status enum('0','1') default '1';
2.使用update删除数据 只需要改动这个状态列的数字
update student2 set status='0' where name='wyk1';
3.当查询的时候，使用条件直接查询状态列即可
比如游戏道具过期了 那么过期的用户就查询不到 实际表里就只改了这个状态栏 那个道具的数据是不用改动的
select * from stu2 where status='1';
### 注意：update修改数据库用户的密码后，需要flush privileges; 刷新数据库 修改其他数据，不需要
DCL
### DCL含义: 控制用户的操作
Database Control Language
数据 控制 语言
## 赋予权限
# 5.6和5.7区别：5.7老版本，grant赋予权限，如果该用户不存在，不会创建用户，5.6和5.7新版本会直接创建用户
# 语法：
grant all on *.* to test@'%' identified by '123';
grant 权限1,权限 on 库.表 to 用户@'主机域' identified by '密码';
# 比如要加上option权限 方法如下;
grant all on *.* to test@'%' identified by '123' with grant option;
## 限制权限（拓展）
# 限制该用户一小时内，查询的次数（连接进入mysql就会查两次）
without max_queries_per_hour
# 限制用户一小时，执行update（改）的次数
max_updates_per_hour
# 限制用户一小时内，连接数据库的次数
max_connections_per_hour
# 限制同一个用户同时可以连接数据库的个数
max_user_connections
# 案例：限制用户一小时内 只能查询3次 只能执行一次update 只能连接一次数据库 同一个用户只能登陆一个
root@localhost:(none)>grant all on *.* to wyk@'%' identified by '123'
-> without max_queries_per_hour 3
-> max_updates_per_hour 1
-> max_connections_per_hour 1
-> max_user_connections 1
-> grant option;
# 像这种限制的权限是没办法回收的 只能删除用户 或者就像下面的 修改它的次数
root@localhost:(none)>grant all on *.* to wyk@'%' identified by '123'
-> with max_queries_per_hour 999
-> max_user_connections 999;
## 回收权限
# revoke语法：
revoke 要回收的权限 on 库.表 from 用户@'主机域';
revoke delete on *.* from wyk@'%';
DQL
### DQL含义： 所有的查都是DQL
Database Query Language
数据 查询 语言
## show
show databases; # 查看数据库
show tables; # 查看表
show tables from 库名; # 查看指定数据库下的表，不用切换数据库
show create database 库名; # 查看建库语句
show create table 表名; # 查看建表语句，也是为了查看注释
show create table 库名.表名; # 查看建表语句，也是为了查看注释（不用切换数据库）
show grants for 用户名@'主机域'; # 查看用户授权语句，也是为了查看该用户的权限
show variables; # 查看所有的内置变量（默认配置）
show variables like '%server'; # 模糊查询（过滤）
show variables like '%server%'; # 用 % 去模糊匹配
show variables like 'server%'; # % 表示匹配指定方位的内容多次或0次
show processlist; # 查看后台执行的sql语句
show full processlist; # 查看完整的后台执行的sql语句
show collation; # 查看所有校验规则
show charset; # 查看所有字符集以及该字符集的默认校验规则
## desc
desc 库.表 # 查看表结构
## 事务控制语句
Transaction Control Language
Database Transaction Language
## select
# 下载练习表 导入到数据库内 该数据导入时 会检测有没有world库 有就删除再创建一个world库 导入数据
[root@db01 ~]# wget http://test.driverzeng.com/MySQL_File/world.sql
root@localhost:(none)> source /root/world.sql
root@localhost:(none)> show databases;
+--------------------+
| Database |
+--------------------+
| world |
+--------------------+
root@localhost:(none)> use world
root@localhost:world> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
## select 常用简单查询
# 查看该表中所有的字段的记录（所有数据）
select * from 库名.表名;
root@localhost:world> select * from city;
# 查看指定字段的所有数据
select 字段1,字段5,字段... from 表名;
root@localhost:world> select id,name,countrycode from city;
## select 行级查询（翻页功能） （默认为 <=指定的数 ）
select 字段1,字段5,字段... from 表名 limit 指定行数;
# 翻页功能，从1开始查到60 查60行
select id,name,countrycode from city limit 60;
# 翻页功能，从121开始查到180 查60行
select id,name,countrycode from city limit 120,60;
# 翻页功能，从181开始查到241 查60行
select id,name,countrycode from city limit 180,60;
## select 条件查询（精确）
select * from 表名 where 字段='记录';
select * from city where countrycode='CHN';
# 多条件查询 or and
select * from city where countrycode='CHN' or countrycode='USA';
select * from city where countrycode='CHN' and district='heilongjiang';
# 条件查询结合行级查询
select * from city where countrycode='CHN' and district='heilongjiang' limit 10;
# 多条件查询用 in 的方法：
select * from 表名 where 字段 in ('要查询的内容一','要查询内容二');
select * from city where countrycode in ('CHN','USA');
## select 模糊查询
select * from 表名 where like '%记录%';
select * from city where countrycode like '%HN';
select * from city where countrycode like '%HN%';
select * from city where countrycode like 'HN%';
# 条件查询结合行级查询（模糊查询可以接精确查询）
select * from city where countrycode like '%HN%' or countrycode='JPN' limit 10;
## select 排序查询 order by
# 顺序排序
select * from 表名 order by 要排序的记录；
# 不加条件 顺序排序
select * from city order by population;
# 加条件 顺序排序
select * from city where countrycode='CHN' order by population;
# 倒序排序
select * from 表名 order by 要排序的记录 desc;
# 不加条件 倒序排序
select * from city order by population desc;
# 加条件 倒序排序
select * from city where countrycode='CHN' order by population desc;
## select 范围查询
select * from 表名 where 要比较的字段 > 指定对比的值;
可用符号： > < >= <= <> !=
select * from city where population < 10000;
# union all 联合查询 将两条查询命令拼接起来 提高效率
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
### select 分组查询 group by
# 语法：
select 字段1,聚合函数(字段2) from 表名 group by 形容词;
# 聚合函数类型如下：
max()：最大值
min()：最小值
sum()：求和
avg()：求平均值`
count()：统计
# 口诀
1.遇到统计想函数
2.形容词前groupby (要查的数据按group by后面的形容词分组)
3.函数中央是名词
4.列名select后添加
## 练习题：
# 题一：统计世界上每个国家的总人口数
# 先想口诀该怎么写
遇到统计想函数：sum()
形容词前groupby：group by countrycode
函数中央是名词：sum(population)
列名select后添加：国家 人口数 countycode,population
mysql[world]> select countrycode,sum(population) from city group by countrycode;
# 题二：统计中国各个省的人口数量(练习)
# 先想口诀该怎么写
遇到统计想函数：sum()
形容词前groupby：group by district
函数中央是名词：sum(population)
名select后添加：省 人口数 district,population
select district,sum(population) from city where countrycode='CHN' group by district;
# 题三：统每个国家的城市数量(练习)
# 先想口诀该怎么写
遇到统计想函数：count()
形容词前groupby：group by countrycode
函数中央是名词：count(name)
名select后添加：国家，城市数量 countrycode,count(name)
select countrycode,count(name) from city group by countrycode;
## 可以用as来指定输出结果时字段的别名
select 字段1 as 要指定的别名,聚合函数(字段2) as 要指定的别名 from 表名 group by 形容词;
select countrycode as 国家,count(name) as 城市数量 from city group by countrycode;
select 高级用法
传统连接
### 多表连查 类似集合 传统连接 逻辑性强 但不是效率最高的
[50,70,80]
## 创建两个表 内容如下 sid都为主键
表名： student
字段：sid sname
记录：001 zhang3
002 li4
003 wang5
# 建表：
create table student(
sid int(3) zerofill,
sname varchar(10));
# 插入内容
insert into student(sid,sname) value(1,'zhang3'),(2,'li4'),(3,'wang5');
# 查看
select * from student;
+------+--------+
| sid | sname |
+------+--------+
| 001 | zhang3 |
| 002 | li4 |
| 003 | wang5 |
+------+--------+
表名： score
字段： sid smark
记录： 001 50
002 70
003 80
# 建表：
create table score(
sid int(3) zerofill,
smark float(3,1));
# 插入内容
insert into score value(1,50),(2,70),(3,80);
# 查看
show tables;
+----------------+
| Tables_in_zls1 |
+----------------+
| score |
| student |
+----------------+
## 要求：查询出wang5的成绩
### 传统连接： 连表查询的前提：两张表，必须有关联的字段
select 字段1,字段2
from 表名1,表名2
where 等价条件
and 条件;
select student.sname,score.smark
from student,score
where student.sid=score.sid
and student.sname='wang5';
## 传统连接，多表查询
select 字段1,字段2
from 表名1,表名2
where 等价条件
and 等价条件
and 等价条件
......
and 条件;
## 练习题（用上面下载的三个表）
# 题一：世界上小于100人的人口城市是哪个国家的？
select country.name as 国家,city.name as 城市,city.population as 城市人口数
from city,country
where city.countrycode=country.code
and city.population < 100;
+----------+-----------+-----------------+
| 国家 | 城市 | 城市人口数 |
+----------+-----------+-----------------+
| Pitcairn | Adamstown | 42 |
+----------+-----------+-----------------+
## 题二：世界上大于10000000人口数量的城市在哪个国家，说什么语言？
select country.name as 国家,country.name as 城市名,city.population as 城市人口,countrylanguage.Language as 国家语言
from city,country,countrylanguage
where city.countrycode=country.code
and country.code=countrylanguage.CountryCode
and city.population > 10000000;
自连接（NATURAL JOIN）
### 自动找到等价条件，前提：两张表的等价条件字段名，必须一样
# 三张表相同的字段：
city 国家代码：countrycode
country 国家代码： code
countrylanguage 国家代码： countrycode
## 语法：
# 自连接查询：两张表
select 字段1,字段2,字段3,字段4
from 表1相同内容字段 natural join 表2相同内容字段
where 条件;
## 说english的城市有哪些，他们的国家代码是什么？
select city.countrycode,city.name,countrylanguage.language
from city natural join countrylanguage
where countrylanguage.language='english';
## 每个国家有几个城市说英语，他们的国家代码是什么？
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言
from city natural join countrylanguage
where countrylanguage.language='english'
group by countrycode;
## 每个国家有几个城市说英语，他们的国家代码是什么？ 按城市数量排序
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言
from city natural join countrylanguage
where countrylanguage.language='english'
group by countrycode order by 城市数量;
内连接（join on）
## 语法：
# 内连接查询：两表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件
where 自己的条件;
# 内连接查询：多表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件A
jion 表2
on 等价条件B
.....
where 自己的条件;
### join on 注意点：大表（字段多的表）在前，小表在后
## 练习题：
# 题一：每个国家有几个城市说英语，他们的国家代码是什么？
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言
from city join countrylanguage
on city.countrycode=countrylanguage.countrycode
where countrylanguage.language='english'
group by city.countrycode;
# 题二：找到城市人口是1410000的城市
select country.name as 国家,city.name as 城市,city.population as 城市人口数
from city join country
on city.countrycode=country.code
and city.population=1410000;
+----------+--------+-----------------+
| 国家 | 城市 | 城市人口数 |
+----------+--------+-----------------+
| Vietnam | Hanoi | 1410000 |
| Zimbabwe | Harare | 1410000 |
+----------+--------+-----------------+
# 题三：世界上大于10000000人口数量的城市在哪个国家，说什么语言？
select country.name,city.name,city.population,countrylanguage.language
from country
join city
on country.code=city.countrycode
join countrylanguage
on country.code=countrylanguage.countrycode
where city.population > 10000000;
+-------+-----------------+------------+-----------+
| name | name | population | language |
+-------+-----------------+------------+-----------+
| India | Mumbai (Bombay) | 10500000 | Asami |
| India | Mumbai (Bombay) | 10500000 | Bengali |
| India | Mumbai (Bombay) | 10500000 | Gujarati |
| India | Mumbai (Bombay) | 10500000 | Hindi |
| India | Mumbai (Bombay) | 10500000 | Kannada |
| India | Mumbai (Bombay) | 10500000 | Malajalam |
+-------+-----------------+------------+-----------+
外连接
## 左外连接 left join 显示左边表的内容，右边表结果显示NULL
select 字段1,字段2,字段3
from 表1 left jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city left join country
on city.countrycode=country.code
and city.population<100;
+------------------------------------+-------------+----------+
| Khan Yunis | PSE | NULL |
| Hebron | PSE | NULL |
| Jabaliya | PSE | NULL |
+------------------------------------+-------------+----------+
## 右外连接 right join 显示右边表的内容，左边表结果显示NULL
select 字段1,字段2,字段3
from 表1 right jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city right join country
on city.countrycode=country.code
and city.population<100;
+-----------+-------------+----------------------------------------------+
| NULL | NULL | South Africa |
| NULL | NULL | Zambia |
| NULL | NULL | Zimbabwe |
+-----------+-------------+----------------------------------------------+
联合查询
#范围查询OR语句
select * from city where countrycode='CHN' or countrycode='USA';
#范围查询IN语句
select * from city where countrycode in ('CHN','USA');
# 替换为联合查询（提高查询效率）：
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
# 联合查询选项：
union ：去重复内容的字段 并合并命令
union all ：不去重复 并合并命令
一般使用时：union all > union
做DBA可了解下以下内容
视图：view
触发器：trigger
触发器&视图简单了解
- 触发器
- 视图
- 存储过程
## 触发器
# 比如说 跳板机有用户 用户信息存在一个表里
jumpserver：跳板机，用户 存在 jumpserver_user
# zabbix监控也有用户 用户信息存在一个表里
zabbix：监控，用户 存在 zabbix_user
那么 当你公司里 新来一个运维 你肯定需要给他开这两个服务的用户 当你设置了触发器以后
你在jumpserver_user表里写一个用户进去 就触发了触发器 它会往zabbix_user也写入这个用户
设置完触发器以后 当你设置的条件满足时就触发你设置好的操作 这就是触发器
## 视图
多表联查时 数据比较大 效率比较低 可以把查出来的结果 做成视图 这样 当我们还要差这个结果时 直接查这个做成视图的表就好了 提高了效率 相当于是一个临时表 实际是不存在的
企业级建表
CREATE TABLE `test_table` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`DOMAIN_CODE` varchar(20) NOT NULL COMMENT '考试单位编号',
`EXAM_NAME` varchar(300) NOT NULL COMMENT '考试名称',
`EXAM_TYPE` int(1) NOT NULL COMMENT '考试类型(正式考试，补考)',
`TARGET_EXAM_ID` bigint(20) DEFAULT NULL COMMENT '关联正式考试的ID(如果是补考，该处是必填)',
`EXAM_PICTURE_PATH` varchar(100) DEFAULT NULL COMMENT '图示路径',
`EXAM_BEGIN_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '考试开始时间',
`EXAM_END_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '考试结束时间',
`EXAM_TIME` int(3) NOT NULL COMMENT '考试时长',
`EXAM_NEED_SCORE` int(5) NOT NULL COMMENT '考试所需积分',
`EXAM_PAPER_TYPE` int(1) DEFAULT NULL COMMENT '考试试卷类型（0固定、1随机）',
`EXAM_SCORE` double(6,2) DEFAULT NULL COMMENT '考试总分(关联试卷后回填)',
`EXAM_PASS_SCORE` double(6,2) NOT NULL COMMENT '考试及格分',
`EXAM_COMMIT_NUM` int(2) NOT NULL COMMENT '参考最大次数',
`EXAM_STATUS` int(1) NOT NULL COMMENT '发布状态0未发布，1已发布',
`EXAM_YEAR` varchar(5) NOT NULL COMMENT '年份',
`EXAM_PAPER_ID` bigint(20) DEFAULT NULL COMMENT '关联试卷ID',
`EXAM_DISCRIPTION` varchar(1000) DEFAULT NULL COMMENT '考试备注',
`OPERATOR_USER_ACCOUNT` varchar(20) NOT NULL COMMENT '修改人',
`OPERATOR_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',
`TARGET_DOMAIN_CODE` varchar(20) DEFAULT NULL COMMENT '发布目标单位编号（发布时回填）',
`RANK` varchar(100) DEFAULT NULL COMMENT '职务级别（发布时回填）',
`EXAM_DIPLOMA_ID` bigint(20) DEFAULT NULL COMMENT '关联证书',
`DIPLOMA_NAME` varchar(200) DEFAULT NULL COMMENT '证书标题(关联证书后回填',
`DIPLOMA_PICTURE_PATH` varchar(200) DEFAULT NULL COMMENT '证书背景图片保存位置(关联证书后回填)',
`INDUSTRY_CODES` varchar(1000) DEFAULT NULL,
`LANGUAGE` int(2) NOT NULL DEFAULT '1' COMMENT 'è¯è¨€ï¼ˆ0:全部,1:汉è¯,2:ç»´è¯,3:è’è¯,4:哈è¯ï¼‰',
`EXT1` int(1) NOT NULL DEFAULT '1' COMMENT '成绩计入学分的字段标识（0 是，1否）',
`EXT2` int(3) DEFAULT NULL COMMENT '成绩所占比例',
`EXT3` varchar(1) DEFAULT NULL,
`EXT4` varchar(1) DEFAULT NULL,
`EXT5` varchar(1) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `DOMAIN_CODE` (`DOMAIN_CODE`),
KEY `EXAM_PAPER_ID` (`EXAM_PAPER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=365 DEFAULT CHARSET=utf8;
## 主键 唯一键可以写最底下 主键是主序列 KEY `DOMAIN_CODE`，KEY `EXAM_PAPER_ID`是普通序列
## 'è¯è¨€ï¼ˆ0:全部,1:汉è¯,2:ç»´è¯,3:è’è¯,4:哈è¯ï¼‰',是乱码 本来是少数民族语
练习题：
# 题1. 建库 要求 > 库名：linux50 字符集：utf8 校验规则：utf8_general_ci
create database if not exists linux50 charset utf8 collate utf8_bin;
# 题2.创建表 并插入数据 要求：
表名：student（学生表）
字段 数据类型要求 是否为空 注释
sno 最多20位 否 学号（主键）
sname 可变长 否 学生姓名
sage 最小整数，非负数 否 学生年龄
ssex 0，1 否 学生性别（1是男，0是女s）默认为男）
sbirthday 时间类型 默认为空 学生生日
class 可变长 否 学生班级
create table student(
sno bigint(20) primary key auto_increment comment '学号(主键)',
sname varchar(10) not null comment '学生姓名',
sage tinyint unsigned not null comment '学生年龄',
ssex enum('0','1') not null default '1' comment '学生性别（1是男，0是女s）默认为男）',
sbirthday datetime default null comment '学生生日',
class varchar(5) not null comment '学生班级'
)
insert into student(sname,sage,ssex,sbirthday,class) value
('徐导',20,'1',now(),'1'),
('曾导',18,'1',now(),'1'),
('李导',25,'1',now(),'2');
# 题3.创建表 并插入数据 要求：
表名：course（课程表）
字段 数据类型要求 是否为空 注释
cno 最多20位 否 课程号（主键）
cname 可变长 否 课程名称
tno 可变长 否 教师编号
create table course(
cno bigint(20) primary key auto_increment comment '课程号(主键)',
cname varchar(10) not null comment '课程名称',
tno varchar(3) not null comment '教师编号'
)
insert into course(cno,cname,tno) value(1,'英语','001'),(2,'语文','002'),(3,'数学','003');
# 题4.创建表 并插入数据 要求：
表名：score（成绩表）
字段 数据类型要求 是否为空 注释
sno 最多20位 否 学号（主键）
cno 最多20位 否 课程号（主键）
mark 浮点数(4,1) 否 成绩
create table score(
sno bigint(20) primary key auto_increment comment '学号（主键）',
cno bigint(20) not null unique key comment '课程号（主键）',
mark float(4,1) not null comment '成绩'
)
## 上面的方法是写了一个不为空的唯一键 下面的方法是把两个主建关联起来
## ENGINE 指定存储引擎
CREATE TABLE `test4` (
`sno` bigint(20) NOT NULL AUTO_INCREMENT,
`cno` bigint(20) NOT NULL,
`mark` float(4,1) NOT NULL,
PRIMARY KEY (`sno`,`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into score(sno,cno,mark) value(1,1,90.0),
(2,1,10.0),
(3,1,60.0),
(1,2,90.0),
(2,2,99.5),
(3,2,80.0),
(1,3,80.5),
(2,3,60.0),
(3,3,88.0);
# 题5.创建表 并插入数据 要求：
表名：teacher（教师表）
字段 数据类型要求 是否为空 注释
tno 最多20位 否 教师编号（主键）
tname 可变长 否 教师姓名
tage 最小整数，非负数 否 教师年龄
tsex 0，1 否 教师性别（1是男，0是女）默认为男）
prof 可变长 是 教师职称
depart 可变长 否 教师部门
create table teacher(
tno bigint(3) zerofill primary key auto_increment comment '教师编号（主键）',
tname varchar(10) not null comment '教师编号（主键）',
tage tinyint unsigned not null comment '教师年龄',
tsex enum('0','1') not null comment '教师性别（1是男，0是女）默认为男）',
prof varchar(10) null comment '教师职称',
depart varchar(10) not null comment '教师部门'
);
## zerofill：零填充 加了这个选项后 写入1时 这里指定了3位 那么会保存为001 只能写整数类型
insert into teacher(tname,tage,tsex,prof,depart)
value('豪豪龙王',18,'1','教学总监','语言系'),
('徐亮伟',50,'1','讲师','文学系'),
('李永义',80,'1','助教','科学系');
### 查询练习：
# 1.查询student表中的所有记录的sname、ssex和class列。
select sname,ssex,class from student;
# 2.查询教师所有的单位即不重复的depart列。
select distinct(depart) from teacher;
# 3.查询student表的所有记录。
select * from student;
# 4.查询score表中成绩在60到80之间的所有记录。
select * from score where mark>=60 and mark<=80;
# 5.查询score表中成绩为85，86或88的记录。
select * from score where mark in (85,86,88);
select * from score where mark=85 or mark=86 or mark=88;
# 6.查询student表中1班或性别为“女”的同学记录。
select * from student where class='1' or ssex='0';
# 7.以class降序查询Student表的所有记录。
select * from student order by class desc;
# 8.以cno升序、mark降序查询Score表的所有记录
select * from score order by cno , mark desc;
# 9.查询2班的学生人数。
select count(class) from student where class='2' group by class;
# 10.查询”熬兴“教师任课的学生成绩。
### 传统连接方法
select teacher.tname,course.cname,student.sname,score.mark
from teacher,course,student,score
where teacher.tno=course.tno
and course.cno=score.cno
and student.sno=score.sno
and teacher.tname='熬兴';
### 内连接方法
select teacher.tname,course.cname,student.sname,score.mark
from teacher join course
on teacher.tno=course.tno
join score
on course.cno=score.cno
join student
on student.sno=score.sno
where teacher.tname='熬兴';
# 11.查询语文课程所有男生的成绩并且查出对应课程的教师名，职称，及所在部门。
### 传统连接方法
select teacher.tname,teacher.prof,teacher.depart,student.sname,course.cname,score.mark
from teacher,course,student,score
where teacher.tno=course.tno
and course.cno=score.cno
and student.sno=score.sno
and student.ssex='1'
and course.cname='语文';
### 内连接方法
select teacher.tname,teacher.prof,teacher.depart,student.sname,course.cname,score.mark
from teacher join course
on teacher.tno=course.tno
join score
on course.cno=score.cno
join student
on student.sno=score.sno
where student.ssex='1'
and course.cname='语文';
# 12.把11题查出的成绩按照降序排序。
select teacher.tname,teacher.prof,teacher.depart,student.sname,course.cname,score.mark
from teacher join course
on teacher.tno=course.tno
join score
on course.cno=score.cno
join student
on student.sno=score.sno
where student.ssex='1'
and course.cname='语文'
order by score.mark desc;