MySQL

MySQL

数据库

创建数据库

语法

CREATE DATABASE [IF NOT EXISTS] 表名 [create_specification [,create_specification]…]

[DEFAULT] CHARACTER SET charset_name

| [DEFAULT] COLLATE collation_name

1. CHARACTER SET：指定数据库采用的字符集，如果不指定字符集，默认utf8
2. COLLATE：指定数据库字符集的校对规则

练习

#创建数据库
#使用指令创建数据库
CREATE DATABASE dp01;
#创建一个使用utf8字符集的db02数据库
create database dp02 character set utf8;
#创建ige使用utf8字符集，并带校对规则的db03数据库
create database dp03 character set utf8 collate utf8_bin;

查看、删除数据库

语法

显示数据库语句

SHOW DATABASES（显示所有数据库）

显示数据库创建语句

SHOW CREATE DATABASE 表名

数据库删除语句（慎用）

DROP DATABASE [IF EXISTS] 表名

练习

#查看当前数据库服务器中的所有数据库
show databases;
#查看前面创建的dp01数据库的定义信息
show create database dp01;
#说明：在创建数据库时，为了规避关键字，可以使用反引号解决
#删除前面创建的dp01数据库
drop database dp01;

备份、恢复数据库

语法

备份数据库（注意：在DOS执行）命令行

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n >文件名.sql

恢复数据库（注意：进入MySQL命令行在执行）

Source 文件名.sql

表

创建

语法

CREATE TABLE table_name

(

​ field1 datatype,

​ field2 datatype,

​ field3 datatype,

)character set 字符集 collate 校对规则 engine 引擎

field：指定列名 datatype：指定列类型（字段类型）

character set：如不指定则为所在数据库字符集

collate：如不指定则为所在数据库校对规则

engine：引擎（后面讲）

练习

#指令创建表
#创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型
#例：user表
#id 整形
#name 字符串
#password 字符串
#birthday 日期
CREATE TABLE `user` ( id INT, `name` VARCHAR ( 255 ), `password` VARCHAR ( 30 ),birthday date);

添加、删除、修改

基本操作

添加列

alter table 表名 add(column datatype [default expr] [,column datatype]…);

修改列

alter table 表名 modify(column datatype [default expr] [,coiumn datatype]…);

删除列

alter table 表名 drop(colum)

删除表

drop table 表名

修改列名

alter table 表名 change 列名 新列名 数据类型

产看表的结构

desc 表名;//可以查看表的列

修改表名：

rename table 表名 to 新表名

修改表字符集：

alter table 表名 character set 字符集；

Mysql数据类型（列类型）

数值类型

整数

说明

使用规范：在能够满足需求的情况下，尽量选择占用空间小的类型

如果没有指定 unsinged，则就是有符号的类型

如何定义一个无符号的整数

create table t10(id tinyint);//默认是有符号的

create table t11(id tinyint unsigned);//无符号的

练习

#使用tinyint来演示范围，有符号-128~127.无符号0~255
#表的字符集，校验规则，存储引擎，使用默认
#1.如果没有指定 unsigned，则就是有符号
#2.如果指定unsigned，则就是无符号
create table t3(
		id tinyint);
#Out of range value for column 'id' at row 1
-- insert into t3 values(-129)
#成功
-- insert into t3 values(-128)
#Out of range value for column 'id' at row 1
-- insert into t3 values(128)
#成功
insert into t3 values(127)
select * from t3
create table t4(
	id tinyint unsigned)
#Out of range value for column 'id' at row 1
-- insert into t4 values(-1)
insert into t4 values(0)
insert into t4 values(255)
#Out of range value for column 'id' at row 1
-- insert into t4 values(256)
select * from t4

bit类型演示

#演示bit类型使用
#说明
#1.bit(m) m 在1~64
#2.添加数据 范围 按照你给定的位数来确定，比如m=8表示一个字节 0~255
#3.显示按照bit
#查询时，仍然可以按照数来查询
create table t5(num BIT(8));
insert into t5 values(1);
insert into t5 values(255);
select * from t5;
select * from t5 where num=1;

小数类型

说明

1.float/double[unsigned] float 单精度，double 双精度

2.decimal[M,D] [unsigned]

• 可以支持更加精确的小数位。M是小数位数（精度）的总数，D是小数点（标度）后的位数
• 如果D是0，则值没有小数点或分数部分。M最大65。D最大是30。如果D被省略，默认是0。如果M被省略，默认是10
• 建议：如果希望小数的精度高，推荐使用decimal

文本类型

字符串的基本使用

char(size) 固定长度字符串 最大255字符

varchar(size) 0~65535 可变长度字符串 最大65532字节

字符串使用细节

细节1

char(4) 这个4表示字符数（最大255），不是字节数，不管是中文还是字母都是放四个，按字符计算

varchar(4) 这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据

细节2

char(4) 是定长(固定的大小)，就是说，即使插入‘aa’，也会占用分配的4个字符的空间

varchar(4)是变长(变化的大小)，就是说，如果插入‘aa’，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配

细节3

什么时候用char，什么时候用varchar

1.如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等

2.如果一个字段是长度不确定，使用varchar

查询速度：char>varchar

二进制类型

时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

CRUD

Insert

语法

insert into table_name[(column [, column…])] values (value [,value…]);

细节说明

1. 插入的数据应与字段的数据类型相同
2. 数据的长度应在列的规定范围内
3. 在values中列出的数据位置必须与被加入的列的排列位置相对应
4. 字符和日期型数据应包含在单引号中
5. 列可以插入控制[前提是该字段允许为空]
6. insert into tab_name(列名…) values(),(),()形式添加多条记录
7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称
8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错

练习

#创建一张商品表goods（id int,goods_name varchar(10),price double）;
#添加2条记录
create table goods(
	id int,
	goods_name varchar(10),
	price double);

insert into goods (id,goods_name,price) values(001,'可乐',3);
insert into goods (id,goods_name,price) values(002,'雪碧',3);

Update

语法

update table_name set col_name1=expr1[,col_name2=expr2…] [where where_definition]

使用细节

1. update语法可以用心智更新原有表行中的各列
2. set子语句指示要修改哪些列和要给予哪些值
3. where子句指示应更新哪些行。如果没有where子句，则更新所有的行
4. 如果需要修改多个字段，可以通过 set 字段1=值1，字段2=值2…

练习

#演示update语句
#在上面创建的employee表中修改表中的记录
#1.将所有员工薪水修改为5000元
#2.将姓名为小妖怪的员工薪水修改为3000元
#3.将姓名为老妖怪的员工薪水在原有基础上增加1000元
update employee set Salary=5000;
update employee set Salary=3000 where user_name='小妖怪';
update employee set Salary=Salary+1000 where user_name='老妖怪';
select * from employee;

Delete

语法

delete from table_name [where where_definition]

使用细节

1. 如果不适应where子句，将删除表中所有数据
2. delete语句不能删除某一列的值（可使用update设为null或者’’）
3. 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句

练习

#delete语句
#删除表中名称为’老妖怪‘的记录
#删除表中所有记录
delete from employee where user_name='老妖怪';
select * from employee;
delete from employee;

Seletc

单表

语法

select [distinct] *|{column1,column2…} from tablename

注意事项

1. select指定查询哪些列的数据
2. column指定列名
3. *代表查询所有列
4. from指定查询那张表
5. distinct可选，指显示结果时，是否去掉重复数据

使用表达式对查询的列进行运算

select *|{column|expression，column|expression…} from tablename;

在select语句中可以使用as语句

select column_name as 别名 from 表名;

在where子句中经常使用的运算符

使用order by子句排序查询结构

select column1,column2,column3…from table order by column asc|desc

注意

1. order by 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名
2. asc升序（默认）、desc降序
3. order by子句应位于select语句的结尾

练习

create table student(
id int,
`name` varchar(32),
chinese int,
english int,
math int
);
insert into student(id ,`name`,chinese,english,math) values(1,'韩顺平',89,78,90);
insert into student(id ,`name`,chinese,english,math) values(2,'张飞',67,98,56);
insert into student(id ,`name`,chinese,english,math) values(3,'宋江',87,78,77);
insert into student(id ,`name`,chinese,english,math) values(4,'关羽',88,98,90);
insert into student(id ,`name`,chinese,english,math) values(5,'赵云',82,84,67);
insert into student(id ,`name`,chinese,english,math) values(6,'欧阳锋',55,85,45);
insert into student(id ,`name`,chinese,english,math) values(7,'黄蓉',75,65,30);
SELECT * from student;

-- 1.查询表中所有学生的信息
-- 2.查询表中所有学生的姓名和对应的英语成绩
-- 3.过滤表中重复数据 distinct
-- 4.要查询的记录，每个字段都相同，才会去重
select * from student;
select `name`,english from student;
select english from student;
select distinct english from student;

-- 统计每个学生的总分
-- 在所有学生总分加10分的情况
-- 使用别名表示学生分数
select `name`,(chinese+english+math) from student;
select `name`,(chinese+english+math+10) from student;
select `name`,(chinese+english+math) as total_score from student;

-- 查询姓名为赵云的学生成绩
-- 查询英语成绩大于90分的同学
-- 查询总分大于200分的所有同学
select * from student where `name`='赵云';
select * from student where english>90;
select * from student where (english+math+chinese)>200;
-- 查询math大于60并且id大于90的学生成绩
-- 查询英语成绩大于语文成绩的同学
-- 查询总分大于200分并且数学成绩小于语文成绩的姓韩的学生
select * from student where math>60 and id>90;
select * from student where english>chinese;
select * from student where (chinese+english+math)>200 and math<chinese and `name` like '韩%';
-- 查询英语分数在80-90之间的同学
-- 查询数学分数为89，90，91的同学
-- 查询所有姓李的学生成绩
-- 查询数学分>80，语文>80的同学
select * from student where english>=80 and english<=90;
select * from student where math in (89,90,91);
select * from student where `name` like '李%';
select * from student where math>80 and chinese>80;

-- 对数学成绩排序后输出【升序】
-- 对总分按从高到低的顺序输出【降序】
-- 对姓李的学生成绩排序输出（升序）
select * from student ORDER BY math;
select `name`,(chinese+english+math) as total_score from student order by total_score desc;
select * from student where `name` like '李%' order by math;

查询加强

使用where子句

使用like操作符

• ​ %：表示0到多个字符
• ​ _：表示单个字符

使用 order by子句

分页查询

语法：select … limit start ,rows.

表示从start+1开始取，取出rows行，start从0开始计算

注意：

如果select语句同时包含group by，having，limit，order by，那么他们的顺序是group by，having，order by，limit

多表

多表查询是指基于两个或两个以上的表查询，查询单个表可能不能满足需求

小技巧：多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集

自链接

自链接是指在同一张表的连接查询

子查询

单行子查询：

单行子查询是指只返回一行数据的子查询语句

多行子查询是指返回多行数据的子查询 使用关键字 in

在多行子查询中使用all操作符

ALL关键字与any关键字类似，只不过上面的or改成and。即:

select …from … where a > all(…);

=> select …from … where a > result1 and a > result2 and a > result3;

即a大于子查询中的每一个，等同于a大于子查询的最大值。

select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30)

在多行子查询中使用any操作符

假设any内部的查询语句返回的结果个数是三个，如:result1,result2,result3,那么，

select …from … where a > any(…);

=> select …from … where a > result1 or a > result2 or a > result3;

即a大于子查询中的任意一个，等同于a大于子查询的最小值即可。

select ename,sal,deptno from emp where sal> any(select sal from emp where deptno=30)

多列子查询

多列子查询是指查询返回多个列数据的子查询语句

（字段1，字段2…）=（select 字段1，字段2 from…）

表复制

自我复制数据：有时为了某个sql语句进行效率测试，需要海量数据时，可以使用此方法为表创建海量数据

insert into 表名 select * from 表名;

去重

思路：
1.先创建一张临时表my_tmp，该表的结构和my_tab02一样
2.把my_tmp的记录 通过 distinct 关键字 处理后 把记录复制到my_tmp
3.清除掉my_tab02记录
4.把 my_tmp 表的记录复制到my_tab
5.drop掉 临时表my_tmp

合并查询

有时在实际应用中，为了合并多个select语句的结构，可以使用集合操作符号 union，union all

1.union all

该操作符用于取得两个结果集的并集。使用该操作符时，不会取消重复行。

2.union

该操作符与 union all相似，但是会自动去掉结果集中重复行

函数

统计函数

统计函数-count

返回行的总数

语法

select count(*)|count(列名) from table_name [where where_definition]

count(*) 和 count(列) 的区别

count(*) ：返回满足条件的记录的行数（包含null）

count(列)：统计满足条件的某列有多少个，但是会排除为null

练习

-- 统计一个班级共有多少学生
-- 统计数学成绩大于90的学生有多少个
-- 统计总分大于250的人数有多少个
select * from student;
select count(*) from student;
select count(id) from student;
select count(*) from student where math>90;
select count(*) from student where (chinese+english+math)>250;

合计函数-sum

sum函数返回满足where条件的行的和

语法

select sum(列名){，sum(列名)…} from tablename [where where_definition]

练习

-- 统计一个班数学总成绩
-- 统计一个班语文、英语、数学各科的总成绩
-- 统计一个班语文、英语、数学的成绩总和
-- 统计一个班级语文成绩平均分
select SUM(math) from student;
select SUM(math),SUM(english),SUM(chinese) from student;
select SUM(math+english+chinese) from student;
select SUM(chinese)/count(*) from student;

合计函数-avg

avg函数返回满足where条件的一列的平均值

语法

select avg(列名){，avg(列名)…} from tablename [where where_definition]

练习

-- 求一个班级数学平均分
-- 求一个班级总分平均分
select AVG(math) from student;
select AVG(math+chinese+english) from student;

合计函数-Max/Min

Max/Min函数返回满足where条件的一列的最大/最小值

语法：

select max/min(列名) from tablename [where where_definition]

练习

#求班级最高分和最低分
#求出班级数学最高分和最低分
select max(math+english+chinese),min(max+english+chinese) from student;
select max(math),min(max) from student;

分组统计

语法

使用group by子句对列进行分组

select column1,column2,column3… from table grop by coiumn

使用having子句对分组后的结果进行过滤

select column1，column2，column3… from table group by column having …

时间日期

时间日期相关的函数

字符串函数

字符串相关的函数

数学函数

数学处理相关的函数

加密和系统函数

加密和系统有关的函数

流程控制

流程控制相关的函数

外连接

左外连接：如果左侧的表完全显示就说是左外连接

右外连接：如果右侧的表完全显示就说是右外连接

语法：

左外连接：select …from 表1left join 表2 on 条件

右外连接：select…from 表1 right join 表2 on 条件

MySQL约束

约束用于确保数据库的数据满足特定的商业规则。

在mysql中，约束包括：not null , unique , primary key , foreign key , 和 check 五种

primary key（主键）

基本使用：

字段名 字段类型 primary key

用于唯一的标识表行的数据，当定义主键约束后，该列不能重复

细节说明：

1. primary key不能重复而且不能为null
2. 一张表最多只能有一个主键，但可以是复合主键
3. 主键的指定方式有两种：
   1. 直接在字段明后指定：字段名 primary key
   2. 在表定义最后写 primary key(列名)
4. 使用desc 表名，可以看到primary key的情况

not null 非空

如果在列上定义了 not null，那么当插入数据时，必须为列提供数据

基本使用：

字段名 字段类型 not null

unique（唯一）

当定义了唯一约束后，该列值是不能重复的

基本使用：

字段名 字段类型 unique

细节说明：

1. 如果没有指定 not null，则unique字段可以有多个null
2. 一张表可以有多个unique字段

foreign key（外键）

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

基本使用：

foreign key(本表字段名) refernces 主表名(主键名或unique字段名)

细节说明：

1. 外键指向的表的字段，要求是primary key或者是 unique
2. 表的类型是innodb，这样的表才支持外键
3. 外键字段的类型要和主键字段的类型一致（长度可以不同）
4. 外键字段的值，必须在主键字段中出现过，或者为null【前提是外键字段允许为null】
5. 一旦建立主外键的关系，数据不能随意删除了

自增长

在某张表中，存在一个id列（整数），我们希望在记录的时候。该列从一开始，自动的增长

基本使用：

字段名 整形 auto_increment

细节说明：

1. 一般来说自增长是和primary key 配合使用的
2. 自增长也可以单独使用【但是需要配合一个unique】
3. 自增长修饰的字段为整数型（虽然小数也可以但是非常少使用）
4. 自增长默认从1开始，可以通过修改 alter table 表名 auto_increment=xxx;

MySQL索引

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，查询速度就可以提高百倍千倍