SQL语句入门

MySQL的连接管理

自带的连接工具（客户端）

• mysql

-u: user指定MySQL的用户
-P:password指定MySQL用户的密码
-S：socket指定socket文件的位置
-h:host指定主机IP地址

-e:exec执行SQL语句
-P：port指定端口

--protocol=name:指定连接方式

• mysqladmin
• ​mysqldump(逻辑备份)

​第三方连接工具（客户端）

• Navicat
• ​SQLmanager
• ​SQLyog

​MySQL启动关闭流程

./etc/int.d/mysqld start
systemctl start mysqld
mysql_safa --选项

关闭

/etc/init.d/musql stop 
systemctl stop mysqld
mysqladmin -uroot -S /data/3309/data/3309.sock shutdown

kill -9 pid ?
kill mysql ?
pkill mysql ?

MySQL实例初始化配置

优先级

~/.my.cnf > --defaults-extra-file=/opt/my.cnf > $basedir/my.cnf > /etc/mysql/my.cnf >

/etc/my.cnf

**注意：**如果启动MySQL加了​​--defaults-file=/etc/my.cnf​​选项，其它位置的配置文件都不读取

思考

# cmake:
socket=/application/mysql/tmp/mysql.sock
#命令行：
--socket=/tmp/mysql.sock
#配置文件：
/etc/my.cnf中的[mysqld]标签下：socket=/opt/mysql.sock
#default参数：
--defaults-file=/tmp/a.txt配置文件中[mysqld]标签下：socket=/tmp/test.sock


/application/mysql/tmp/mysql.sock
/tmp/mysql.sock
/opt/mysql.sock
/tmp/test.sock

mysqld --defaults-file=/tmp/a.txt --socket=/tmp/mysql.sock

/tmp/mysql.sock

## 结论
默认配置，优先级
1.命令行
2.配置文件
  - ~/.my.cnf
  - --defaults-extra-fila=/opt/my.cnf
  - $basedir /my.cnf
  - /etc/mysql/my.cnf
  - /etc/my.cnf
3.编译参数

初始化配置的作用

1）影响实例的启动（mysql）[影响服务端的启动]

2. 影响到客户端的连接

 [mysqld] [server] ## 这两个标签下的配置，都是来影响服务端启动的

[mysql] [mysqladmin] [mysqldump] ## 这几个标签，影响对应的客户端命令
[client]  ## 这个标签，影响所有的客户端命令

注意：修改客户端配置，不需要重启mysql,修改服务端配置[mysqld]需要重启mysql

[mysqld]
skip_name_resolve
datadir=/application/mysql
socket=/application/mysql/tmp/mysql.sock
datadir=/application/mysql/data
server_id=10
user=mysql

[mysql]
prompt="\u@\h:\d>"

[client]
password=123
user=root
socket=/opt/mysql.sock

MySQL的SQL语句

客户端命令

• mysql

## 查看命令帮助
?  \?   help  \h

## 查看状态
stayus  \S

## 退出
exit  quit   \q

## 结束当前的SQL语句
\c

## Ctrl + c
MySQL5.6中：退出MySQL 
MySQL5.7中：结束当前SQL语句，类似于\c

## 在MySQL中执行系统命令
system    \!

## 临时将操作记录到指定的文件中
tee   \T
tee /tmp/mysql.log
\T  /tmp/mysql.log

## 切换数据库
use  \u
use  mysql
\u   mysql

## 导入数据
source  \.
source  /tmp/yjt.sql
\.  /tmp/yjt.sql

## 导出数据
mysqldump -u用户名 -p密码 数据库名 > 导出的文件名

## 格式化（key:value） 方式，显示数据
\G
mysql >select * from mysql.user\G

### 客户端配置，显示当前所在数据库及登录用户
[client]
prompt="\u@\h:\d>"

• mysqladmin

mysqladmin password '密码' # 修改密码或者设置密码
mysqladmin shutdown  # 关闭mysql服务
mysqladmin ping # 检测MySQL是否存活
mysqladmin status # 查看MySQL的状态
mysqladmin variables #查看MySQL的默认配置（内置变量）
mysqladmin create 库名 #在库外创建数据库
mysqladmin drop 库名 #在库外删除数据库
mysqladmin reload # 重新加载数据库
mysqladmin flush-log #刷新授权表

## 注意：有密码加  -u  -p

SQL层的SQL语句

什么是SQL语句

结构化的查询语句

标准：SQL-92

SQL语句的分类

检查SQl语句的进程

show processlist;

DDL

Database Definition Language
 数据     定义        语言
 
 # 开发规范：
 (01) 表名不能大写,数字开头,16个字符串
 (02) 表名和业务有关
 (03) drop 语句禁止
 (04) 选择合适的数据类型
 (05) 必须要有主键
 (06) 列尽量非空约束
 (07) 减少外键约束
 (08) 必须设置存储引擎和字符集
 (09) 列必须要有注释
 (10) 对于非负数设置数据类型约束--无符号
 
 ## 库
 # 增
 语法：
 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;
 create database if not exists 库名 charset utf8 collate utf8_bin;
 如果没有这个库名就创建，如果有就不创建
 ### 源码安装自己编译的时候可以设定utf8，二进制安装默认latin1
 
 # 删
 drop database 库名；
 
 # 改
 修改字符集
 alter database yjt charset latin1;
 
 ## DQL查看数据库的字符集
 show create database yjt;
 +----------+----------------------------------------------------------------+
 | Database | Create Database                                                |
 +----------+----------------------------------------------------------------+
 | yjt      | CREATE DATABASE `yjt` /*!40100 DEFAULT CHARACTER SET latin1 */ |
 +----------+----------------------------------------------------------------+
 
 ## 表
 # 增
 create table 表名（字段1 数据类型 约束,字段 数据类型 约束...）
 建表，至少要给的是 字段名称和数据类型
 create table yjt.student(id int not null primary key auto_increment,name varchar(10),age tinyint,gender enum('0','1'));
 
 create table yjt.student2(
 id int,
 name varchar(10),
 age tinyint,
 gender enum('0','1'));
 
 ## 数据类型
 int:    整数  -2^31 ~ 2^31 -1(减一，如果非负，那么就能取到255)
 varchar:    字符类型 （变长）（它可以是不达到这个字符串要求量）
 char:   字符类型（定长） （必须达到字符串要求量）
 smallint:   较小整数数据类型（-2^15到2^15-1）
 mediumint:  中型整数数据类型
 bigint:     较大整数数据类型（-2^63到2^63-1）
 float:  浮点数（小型单精度四个字节浮点数）
 double: 常规双精度（八个字节浮点数）
 decimal: 定点数（包含整数部分，小数部分或同事包括两者的精确值数值）
 bit: 位字段值
 
 name varchar(10)
 可以不达到10位
 
 phone char(11)
 必须到11位
 
 
 tinyint: 整数 -128 ~ 127
 enum: 枚举类型
 enum('A','B','C','D')
 detatime:时间类型  年月日时分秒
 
 ## 约束
 null:      可以为空
 not null:   非空
 primary key:    主键（唯一且非空的） 一张表只能有一个主键
 auto_increment: 自增（此列必须是：primary key或者unique key）
 unique key:   唯一键，单独的唯一的    唯一键 + not null
 default: 默认值
 unsigned: 无符号（非负）
 comment: 注释
 
 create table yjt.student2(
 id int primary key auto_increment comment '学号',
 name varchar(10) not null comment '学生姓名',
 age tinyint unsigned not null default 18 comment '学生年龄',
 gender enum('0','1') not null default '1' comment '学生性别',
 phone char(11) not null unique key comment '学生手机号',
 birtime datetime  not null comment '学生出生日期',
 cometime datetime  not null comment '学生报到时间');
 
 id  name    age gender  phone   bir come
 整型  字符串类型   最小整型    枚举类型    字符串类型   datetime  timestamp datetime  timestamp
 int varchar、char    tynint  enum            
 
 # 删
 drop table student3;
 
 ## 删用户
 drop user yjt@'%';
 
 # 改
 alter table 表名 rename 新表名；
 alter table student rename abcdef;
 
 ## 添加字段（将字段添加在表的最后一列）
 alter table student add zhangsan char(10) not null;
 alter table student3 add ppppp defaulf 'abc'; ## 带默认值添加字段
 
 ## 添加多个字段
 alter table student add zhangsan char(10) not null,add lisi char(5);
 
 ## 按指定位置添加字段
 alter table student add zhangsan char(5) after lisi;
 
 ## 将字段添加在最前面一列
 alter table student add wangwu char(1) first;
 
 ## 删除字段
 alter table student drop  zhangsan;
 
 ## 修改数据类型和属性
 alter table student modify abc enum('0','1') default '1';
 
 ## 修改字段名  数据类型 属性
 alter table student change abc aaa char(10) default '123';

DML

Data Manipulation Language
 数据  操作         语言
 
 所有的DML都是操作表内容的
 ## 增
 # 插入单条数据
 insert into student(id,name,age,phone,cometime,birtime) value('1','zhangsan','23','12345678912',NOW(),'1999-03-03');
 
 # 插入多条数据
 insert into student1(id,name,age,phone,birtime,cometime) value
 ('3','qqq','22','12312312312','2000-01-01',NOW()),
 ('4','www','23','11122233345','1999-02-03',NOW());
 
 # 默认字段不加
 insert into student(name,bir,phone) value('zhangsan',NOW(),'123');
 
 # 不规范写法
 insert student value(7,'lisi',18,'m',NOW(),'122',NOW());
 
 ## 删（危险）
 使用delete 一定要加条件，不加会删除所有的表
 delete from student;(不要这么删除，会把这个库下的表全部删除)
 
 # 加条件
 delete from student1 where name='zzz' and id='7'; ## 删除同时满足这两个条件的
 
 # 想全部删除也要加
 delete from student1 where 1=1;
 
 注意：一般在删除数据时，我们会根据唯一性的字段，进行删除
 delete from mysql.user where user='yjt' and host='172.16.1.%';
 
 ## 改
 使用update一定要加条件
 update
 update student1 set id=5 where name='www';  # 把名字为www的id改为5
 
 ## 给开发人员开用户的权限一般只有增删改查
 insert delete update select
 ## 但是也可以把改当作“删”用
 insert update select
 
 ## 使用update替代delete删除数据
 1.给表中，加一个状态列
 alter table student add status enum('0','1') default '1';
 
 2.使用delete删除数据
 update student1 set status='0' where name='qqq'; ## 和开发约定，0为不存在，1为存在
 
 3.查询的时候，使用where条件查询
 select * from student1 where status='1';  ## 就“等于”删除了，这样做的好处就是，数据不会丢，万一是用户的误操作，我们还能帮他们找回数据
 
 注意：update修改数据库用户的密码，是要flush privileges;修改其他数据，不需要。
 
 # 查询用户的权限
 show grants for yjt@'172.16.1.%';
 +-----------------------------------------------------------+
 | Grants for yjt@172.16.1.%                                 |
 +-----------------------------------------------------------+
 | GRANT SELECT, INSERT, UPDATE ON *.* TO 'yjt'@'172.16.1.%' |
 +-----------------------------------------------------------+
 
 # 查询字符集
 show variables like '%char%';
 +--------------------------+-------------------------------------------+
 | Variable_name            | Value                                     |
 +--------------------------+-------------------------------------------+
 | character_set_client     | utf8                                      |
 | character_set_connection | utf8                                      |
 | character_set_database   | utf8                                      |
 | character_set_filesystem | binary                                    |
 | character_set_results    | utf8                                      |
 | character_set_server     | utf8                                      |
 | character_set_system     | utf8                                      |
 | character_sets_dir       | /application/mysql-5.7.30/share/charsets/ |
 +--------------------------+-------------------------------------------+

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，权限2 on 库.表 to 用户@'主机域' identtified by '密码'；
 grant insert,delete,update,select on *.* to yjt@'172.16.1.%' identified by '123';
 
 grant insert,delete,update,select on *.* to yjt2@'localhost' identified by '123' with grant option;
 
 grant all on *.* to yjt3@'%' identified by '123' with max_queries_per_hour 3;
 max_queries_per_hour 3 ## 限制该用户一小时内，只能查询3次
 grant all on *.* to yjt4@'%' identified by '123' 
 without max_queries_per_hour 3
 max_updates_per_hour 1      ## 限制用户一小时，只能执行一次update
 max_connections_per_hour 1  ## 限制用户一小时内，只能连接一次数据库
 max_user_connections 1      ## 限制用户，只能同时一个用户连接
 grant option;
 
 ### 回收权限
 revoke
 revoke 权限 on 库.表 from 用户@'主机域'；
 revoke delete on *.* from yjt@'%';

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%';      # 模糊查询（过滤）
 
 show processlist;                   # 查看后台执行的sql语句
 show full processlist;              # 查看完整的后台执行的sql语句
 show collation;                     # 查看所有校验规则
 show charset;                       # 查看所有字符集以及该字符的默认校验规则
 
 
 ## desc
 desc 库.表                         # 查看表结构
 查看执行计划
 
 ## explain  查看执行计划  （索引）
 
 ## select 常用简单查询
 select * from city; ## 查看该表中所有的字段的记录（所有记录）(企业里不能用，数据太多直接挂了)
 select id,name,countrycode from city;       ## 查看指定字段的所有数据
 
 ## select 行级查询（翻页功能）
 select id,name,countrycode from city limit 10;
 
 select id,name,countrycode from city limit 10,10;
 ## 翻页功能，从第11个开始查10个
 select id,name,countrycode from city limit 60,60;
 ## 翻页功能，从第61个开始查60个，参考京东购物页面
 
 ## select  条件查询
 select * from city where countrycode='CHN';
 select * from city where countrycode='CHN' or countrycode='USA';
 select * from city where countrycode='CHN' and district='zhejiang';
 
 ### 条件查询结合行级查询
 select * from city where countrycode='CHN' and district='zhejiang' limit 10;
 
 ## select 模糊查询
 select * from city where countrycode like '%HN' or countrycode='JPN';
 select * from city where countrycode like '%HN%' or countrycode='JPN';
 select * from city where countrycode like 'HN%' or countrycode='JPN';
 
 ## select 排序查询 order by
 select * from city where countrycode='CHN' order by population;     ## 序列排序
 select * from city where countrycode='CHN' order by population desc;        ## 倒序排序
 
 ### 不带条件的排序查询（一般没人会这么用。。。）
 select * from city order by population;
 ## 序列排序
 select * from city order by population desc;
 ## 倒序排序
 
 ## select 范围查询
 select * from city where population > 10000000;
 > < >= <= <> !=
 
 # in:
 select * from city where countrycode in ('CHN','USA');
 
 # or:
 select * from city where countrycode='CHN' or countrycode='USA';
 
 # union all 联合查询
 select * from city where countrycode='CHN' union all select * from city where countrycode='USA'; #(与in 一样，执行效率更高) 
 
 ## 不重复查询
 select distinct 字段名 from 数据表
 
 ## select 分组查询 group by
 group by + 聚合函数
 聚合函数：
 max()；最大值
 min():最小值
 sum():求和
 avg():求平均值
 count（）：统计
 
 1.遇到统计想函数
 2.形容词前groupby
 3.函数中央是名词
 4.列名select后添加
 
 # 统计世界上每个国家的总人数
 遇到统计想函数：sum()
 形容词前groupby：group by countrycode
 函数中央是名词：sum(population)
 列名select后添加：国家  人口数    countycode,population
 
 select countrycode,sum(population) from city group by countrycode;
 
 select countrycode as 国家,sum(population) as 人口数 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;

select高级用法

传统连接

## 多表连查
 select * from student;
 +------+--------+
 | sid  | sname  |
 +------+--------+
 |  001 | zhang3 |
 |  002 | li4    |
 |  003 | wang5  |
 +------+--------+
 
 select * from score;
 +------+-------+
 | sid  | smark |
 +------+-------+
 |  001 |    50 |
 |  002 |    70 |
 |  003 |    80 |
 +------+-------+
 
 ## 查询出wang5的成绩
 select student.sname,score.smark 
 from student,score 
 where student.sid=score.sid 
 and student.sname='wang5';
 +-------+-------+
 | sname | smark |
 +-------+-------+
 | wang5 |    80 |
 +-------+-------+
 
 连表查询的前提：两张表，必须有关联的字段
 
 # 世界上小于100人的人口城市是哪个国家的？
 select country.name as 国家,city.name as 城市,city.population as 城市人口数
 from city,country
 where city.countrycode=country.code
 and city.population < 100;
 
 ### 世界上等于1410000人的城市在那些国家？
 select country.name as 国家,city.name as 城市,city.population as 城市人口数 
 from city join country 
 on city.countrycode=country.code 
 and city.population=1410000;
 
 
 +----------+-----------+-----------------+
 | 国家     | 城市      | 城市人口数      |
 +----------+-----------+-----------------+
 | Pitcairn | Adamstown |              42 |
 +----------+-----------+-----------------+
 
 
 ## 世界上大于10000000人口数量的城市在哪个国家，说什么语言？
 国家名    城市名     城市人口数量   语言
 
 select country.name as 国家名,city.name as 城市名,city.population as 人口数,countrylanguage.language as 语言 
 from country,city,countrylanguage 
 where country.code=city.countrycode 
 and country.code=countrylanguage.countrycode 
 and city.population > 10000000;

自连接（NATURAL JOIN）

自动找到等价条件，前提：两张表的等价条件字段名，必须一样
 city   国家代码：countrycode
 country 国家代码： code
 countrylanguage 国家代码： countrycode
 
 SELECT city.name,city.countrycode ,countrylanguage.language ,city.population
 FROM  city NATURAL  JOIN  countrylanguage 
 WHERE population > 1000000;
 
 
 ## 说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 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;
 
 
 ## 世界上大于10000000人口数量的城市在哪个国家，说什么语言？
 1 jion 2 on A
 
 表1 jion 表2 on 等价条件A join 表3 on 等价条件B
 
 ****   传统连接  ****
 select country.name,city.name,city.population,countrylanguage.language
 from country,city,countrylanguage
 where country.code=city.countrycode
 and country.code=countrylanguage.countrycode
 and city.population > 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;
 
 
 join on 注意：大表在前，小表在后

外连接

## 左外连接 left join
 select city.name,city.countrycode,country.name  
 from city left join country  
 on city.countrycode=country.code  
 and city.population<100;
 
 +----------+-------------+------+
 | name     | countrycode | name |
 +----------+-------------+------+
 | Kabul    | AFG         | NULL |
 | Qandahar | AFG         | NULL |
 | Herat    | AFG         | NULL |
 +----------+-------------+------+
 
 
 ## 右外连接 right join
 select city.name,city.countrycode,country.name  
 from city right join country  
 on city.countrycode=country.code 
 and city.population<100;
 
 +------+-------------+-------------+
 | name | countrycode | name        |
 +------+-------------+-------------+
 | NULL | NULL        | Aruba       |
 | NULL | NULL        | Afghanistan |
 | NULL | NULL        | Angola      |
 +------+-------------+-------------+

联合查询

#范围查询OR语句
 mysql> select * from city where countrycode='CHN' or countrycode='USA';
 #范围查询IN语句
 mysql> select * from city where countrycode in ('CHN','USA');
 
 
 # 替换为：
 mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
 union：去重复合并
 union all ：不去重复
 使用情况：union < union all
 
 
 视图：view
 触发器：trigger

# 事务控制语句
 Transaction Control Language
 Database Transaction Language

字符集设置

操作系统设置字符集

## CentOS6
 [root@db01 ~]# source /etc/sysconfig/i18n
 [root@db01 ~]# echo $LANG
 zh_CN.UTF-8
 
 
 ## CentOS7
 [root@db04 tmp]# vim /etc/locale.conf 
 LANG="en_US.UTF-8"
 
 [root@db04 tmp]# echo $LANG
 en_US.UTF-8

连接工具设置字符集

Xshell

## windows
 GB2312  国标
 GBK     国标扩

建库建表级别设置字符集

## 建库设置字符集
 create database zls111 charset utf8 collate utf8_general_ci;
 
 ## 建表设置字符集
 create table test(
 id int,
 name varchar(10)
 ) default charset utf8;

数据库配置设置字符集

vim /etc/my.cnf
 [mysqld]
 character_set_server=utf8

建表语句

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` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '考试开始时间',
             `EXAM_END_TIME` datetime 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;
             ## 关联

零填充

create table teacher(
 tno bigint(3) zerofill primary key auto_increment comment '教师编号（主键）',
 tname 
 )
 
 zerofill 零填充  ##用来自动补全int后面的数字，如 001   010  int（1）实际上限制不住位数，需要配合填充使用

表一

表名：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');

表二 表名：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');

表三

表名：score（成绩表）

字段 数据类型要求 是否为空 注释 sno 最多20位 否 学号（主键） cno 最多20位 否 课程号（主键） mark 浮点数(4,1) 否 成绩

create table score(
     sno bigint(20) not null comment '学号（主键）',
     cno bigint(20) not null comment '课程号（主键）',
     mark float(4,1) not null comment '成绩'
 );
 
 
 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);

表四

表名：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 零填充
 
 
 insert into teacher(tname,tage,tsex,prof,depart) 
 value('曾志高翔',18,'1','教学总监','语言系'),
 ('徐亮伟',50,'1','讲师','文学系'),
 ('李永义',80,'1','助教','科学系');
 

作业3

1.查询student表中的所有记录的sname、ssex和class列。

select sname,ssex,class from student1;

2.查询教师所有的单位即不重复的depart列。

select distinct（depart） from teacher;

3.查询student表的所有记录。

select * from student1;

4.查询score表中成绩在60到80之间的所有记录。

select * from score where mark >= 60.0 and mark <= 80.0;

5.查询score表中成绩为85，86或88的记录。

select * from score where mark in ('85.0','86.0','88.0');

6.查询student表中1班或性别为“女”的同学记录。

select * from student1 where class='1' or ssex='0';

7.以class降序查询Student表的所有记录。

select * from student1 order by class desc;

8.以cno升序、mark降序查询Score表的所有记录

select * from score order by cno,mark desc;

9.查询2班的学生人数。

select count(class) as 二班人数 from student1 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;

select * from course;
 课程号  课程名称  教师编号
 +-----+--------+-----+
 | cno | cname  | tno |
 +-----+--------+-----+
 |   1 | 英语   | 001 |
 |   2 | 语文   | 002 |
 |   3 | 数学   | 003 |
 +-----+--------+-----+
 
 select * from score;
 学号   课程号  成绩
 +-----+-----+------+
 | sno | cno | mark |
 +-----+-----+------+
 |   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 |
 +-----+-----+------+
 
 select * from student;
 学号   学生姓名  年龄   性别    生日                班级
 +-----+--------+------+------+---------------------+-------+
 | sno | sname  | sage | ssex | sbirthday           | class |
 +-----+--------+------+------+---------------------+-------+
 |   1 | 徐导   |   20 | 1    | 2022-08-12 16:49:00 | 1     |
 |   2 | 曾导   |   18 | 1    | 2022-08-12 16:49:00 | 1     |
 |   3 | 李导   |   25 | 1    | 2022-08-12 16:49:00 | 2     |
 +-----+--------+------+------+---------------------+-------+
 
 select * from teacher;
 编号      名字      年龄      性别      教师职称    教师部门
 +-----+--------------+------+------+--------------+-----------+
 | tno | tname        | tage | tsex | prof         | depart    |
 +-----+--------------+------+------+--------------+-----------+
 | 001 | 曾志高翔      |   18 | 1    | 教学总监     | 语言系     |
 | 002 | 徐亮伟        |   50 | 1    | 讲师         | 文学系    |
 | 003 | 李永义        |   80 | 1    | 助教         | 科学系     |
 +-----+--------------+------+------+--------------+-----------+
 
 select student.sname,score.mark,teacher.tname,teacher.prof,teacher.depart
  from student,score,teacher
  where teacher.tno=course.tno;