第六章 SQL基础应用

第六章 SQL基础应用

1，SQL介绍

1，结构化的查询语言

2，5.7以后符号SQL92标准的严格模式

3，通过sql_mode参数控制

2，常用SQL分类

2.1 DDL 数据定义语言

2.2 DCL 数据控制语言

2.3 DML 数据操作语言

2.4 DQL 数据查询语言

3，数据类型，表属性，字符集

3.1，数据类型

1，作用：保证数据的规范性和标准性

2，数值类型

3，字符类型

4，时间类型

5，二进制类型

3.2，表属性

1，列属性

• 约束
  • 主键约束：primary key （必填，不能重复，唯一）
  • 非空约束：not null
  • 唯一约束：unique key
  • 无符号约束（unsigned，针对数值列，不能为负数）
• 其他属性
  • key：索引，应用在某列上
  • default：默认值
  • auto_increment：自增长
  • commit：注释

2，表属性

• 存储引擎：InnoDB（默认）
• 字符集和校对（排序）规则：utf8 utf8mb4

3.3，字符集

1，字符集

• utf8
• utf8mb4：支持emoji字符集

2，校对规则（排序规则）

• 大小写是否敏感
• ci结尾的校对规则，不区分大小写

4，DDL语句

4.1，库定义

1，创建

#创建语句
create database 库名;

#查看mysql支持的字符集
mysql> show  charset;
或
mysql> show character set;

#查看mysql支持的校对规则
mysql> show collation;

#查看建库信息:字符集、校对规则
mysql> show create database dbname;

#建库规范
（1）不能使用数字开头
（2）大小写敏感，建议不要使用大写字母
注意在使用一些客户端如navicat时，建库是不区分大小写，因为客户端不知道服务器是linux还是windows，而windows下的mysql中数据库、表名均不区分大小写。

(3)建库是指定字符集
（4）库名和业务名有关（通常开发人员确定）

2，删除

#生产环境严格禁止该操作
mysql>drop  database   dbname;

3，修改

mysql> alter database dbname charset  charsetname;
mysql> alter database dbname collate collatename;
注意：修改后的字符集应该是原字符集的超集

4，查询

mysql> show databases;
mysql> show create database dbname;

4.2，表定义

1，创建

#语法：
create table tablename(
列1 属性,
列2 属性,
...
)存储引擎 字符集 校对规则;
#属性：数据类型、约束、其他属性

#举例CREATE TABLE stu(
id              INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号',
sname      VARCHAR(255) NOT NULL COMMENT '学生姓名',
sage         TINYINT NOT NULL COMMENT '年龄',
sgender    ENUM('M','F','N') COMMENT '性别',
sfz            CHAR(18) NOT NULL UNIQUE COMMENT '身份证号',
intime       DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_bin COMMENT '学生表';
CREATE TABLE stu(
id              INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号',
sname      VARCHAR(255) NOT NULL COMMENT '学生姓名',
sage         TINYINT NOT NULL COMMENT '年龄',
sgender    ENUM('M','F','N') COMMENT '性别',
sfz            CHAR(18) NOT NULL UNIQUE COMMENT '身份证号',
intime       DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_bin COMMENT '学生表';

#规范：
表名小写
不能数字开头
注意字符集和存储引擎
表名和业务相关
选择合适的数据类型
每个列要有注释
每个列设置为非空，无法保证非空，则使用默认值

2，删除

drop table 表名;
注意：生产环境严禁使用

3，修改

#修改前
mysql>show tables;		查看所有表
mysql>desc 表名;		查看字段信息
mysql>show create table 表名;		查看建表信息

#语法
alter table 表名 ...

#示例：
1. 添加列		ALTER TABLE stu ADD qq VARCHAR(20) COMMENT 'QQ号';	在最后追加列
2. 插入列		ALTER TABLE stu ADD wechat VARCHAR(255)  COMMENT '微信号' AFTER sname;
			  在sname列后面插入列,没有办法在某列前添加，需要使用该方法在某列后边添加
3. 在表的最前边添加列		ALTER TABLE stu ADD num INT COMMENT 'TEST' FIRST;
4. 删除列		ALTER TABLE stu DROP num;
5. 修改列的数据类型		ALTER TABLE stu MODIFY sname VARCHAR(128) NOT NULL COMMENT '学生姓名' CHARSET utf8 COLLATE utf8_bin;
需要完整的写出新的列属性。
6. 修改列名		ALTER TABLE stu CHANGE sage age INT not null comment '年龄';
同样需要指定列的属性。

#修改列的数据类型
ALTER  TABLE  表名称  MODIFY  列名称  新的数据类型;

#修改列名
ALTER  TABLE  表名称  CHANGE  列名称  新列名称  新数据类型;

4，删除表数据

TRUNCATE TABLE stu;

#完全删除表内的数据行，同DML的delete from tablename语句。

#与delete不同的地方：
不做日志记录
不启动事务
降低表的水位线为0，自增列从1重新开始。

#应用场景：
表数据经过多次delete后，形成很多碎片（自增列断续），需要重新整理表时，使用。

5，复制表结构

CREATE TABLE ceshi LIKE stu;
#创建一个和stu结构完全一致的表

5，DML语句

（1）insert：同时录入多行数据
INSERT INTO stu()values(),(),()...();

（2）update：UPDATE tablename set 列名1=值1,列名2=值2 WHERE .....
	注意条件的使用，慎用！！！！

（3）delete：DELETE FROM tablename WHERE ......
	注意条件的使用，慎用！！！！

注意：实际使用中往往利用update配合状态列字段，实现伪删除（保证查不到）。

6，DQL语句

6.1，单独使用

1，#select@@xxx，查看系统参数
select @@port;查看端口号
select @@basedir;查看基础目录
select @@datadir;
select @@socket;Unix 套接字文件
select @@server_id;
select @@identity;下次要使用的自增列的值


2，#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;

6.2，from子句

SELECT 列1,列2,...,列n FROM 表名
SELECT * FROM 表名
注意：最好不要对大表操作

6.3，where子句

1，#配合等值查询

2，#配合比较操作
等于: =
大于: >
大于等于: >=
小于: <
小于等于: <=
不等于: != 或 <>
3，#配合逻辑运算
&&（逻辑与也可以写成and）
||（逻辑或也可以写成or） 
not（逻辑非）

4，#配合模糊查询
%：表示任意0个或多个字符
_： 表示任意单个字符
[ ]：表示括号内所列字符中的一个（类似正则表达式）
[^ ] ：表示不在括号所列之内的单个字符

5，#配合in语句
作用：查询某个范围内的数据

6，#配合between ... and...
作用：检索项在给定的两个值之间，包含两个端点值

6.4，group by子句

1，#作用
根据by后面的条件进行分组，方便统计，by后面跟一个列或多个列。
需要和聚合函数配合使用

2，#常用聚合函数
max():最大值
min():最小值
avg():平均值
sum():求和
count():行个数
group_concat():列转行

3，#group by + 聚合函数
遇到统计想函数
示例：
SELECT countrycode,SUM(population) FROM city  GROUP BY countrycode;

6.5，having子句

#类似where，group by统计完后再执行
作用：筛选所需的内容

示例：从城市表中统计中国每个省的总人口数，且总人口数小于100万。
SELECT province ,SUM(population) 
FROM city 
WHERE countrycode='zn' 
GROUP BY province 
HAVING SUM(population)<1000000;

6.6，order by子句

order by 排序列
逆序：desc

6.7，limit子句

1，#通常配合order by使用，用来取结果的多少个。

2，#LIMIT M OFFSET N
结果集中，跳过N行，取M行。

3，#LIMIT 5,2
结果集中，从第6行开始，取2行。
说明：第一个参数从0开始索引

4，#LIMIT M
相当于LIMIT M OFFSET 0

6.8，distinct子句

作用：去重复
SELECT DISTINCT FROM city;

6.9，quion联合查询

SELECT * FROM city WHERE countrycode='zn'
UNION ALL
SELECT * FROM city WHERE countrycode='us'
UNION ALL 
...

#说明：
（1）通常会把in语句和or语句改写成union all，以提高性能。
（2）前后的列结构需要一致
（3）union去重复，union all不去重复

6.10，多表连接

#试验使用的表
	student
		sno:学号
		sname：姓名
		sage：年龄
		ssex：性别
	teacher
		tno：教师编号
		tname：教师姓名
	cource
		cno：课程编号
		cname：课程名称
		tno：教师编号
	score
		sno：学号
		cno：课程编号
		score：成绩