MySQL
数据库
创建数据库
语法
CREATE DATABASE [IF NOT EXISTS] 表名 [create_specification [,create_specification]…]
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
- CHARACTER SET:指定数据库采用的字符集,如果不指定字符集,默认utf8
- 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…]);
细节说明
- 插入的数据应与字段的数据类型相同
- 数据的长度应在列的规定范围内
- 在values中列出的数据位置必须与被加入的列的排列位置相对应
- 字符和日期型数据应包含在单引号中
- 列可以插入控制[前提是该字段允许为空]
- insert into tab_name(列名…) values(),(),()形式添加多条记录
- 如果是给表中的所有字段添加数据,可以不写前面的字段名称
- 默认值的使用,当不给某个字段值时,如果有默认值就会添加,否则报错
练习
#创建一张商品表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]
使用细节
- update语法可以用心智更新原有表行中的各列
- set子语句指示要修改哪些列和要给予哪些值
- where子句指示应更新哪些行。如果没有where子句,则更新所有的行
- 如果需要修改多个字段,可以通过 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]
使用细节
- 如果不适应where子句,将删除表中所有数据
- delete语句不能删除某一列的值(可使用update设为null或者’’)
- 使用delete语句仅删除记录,不删除表本身。如要删除表,使用drop table语句
练习
#delete语句
#删除表中名称为’老妖怪‘的记录
#删除表中所有记录
delete from employee where user_name='老妖怪';
select * from employee;
delete from employee;
Seletc
单表
语法
select [distinct] *|{column1,column2…} from tablename
注意事项
- select指定查询哪些列的数据
- column指定列名
- *代表查询所有列
- from指定查询那张表
- 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
注意
- order by 指定排序的列,排序的列既可以是表中的列名,也可以是select语句后指定的列名
- asc升序(默认)、desc降序
- 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
用于唯一的标识表行的数据,当定义主键约束后,该列不能重复
细节说明:
- primary key不能重复而且不能为null
- 一张表最多只能有一个主键,但可以是复合主键
- 主键的指定方式有两种:
- 直接在字段明后指定:字段名 primary key
- 在表定义最后写 primary key(列名)
- 使用desc 表名,可以看到primary key的情况
not null 非空
如果在列上定义了 not null,那么当插入数据时,必须为列提供数据
基本使用:
字段名 字段类型 not null
unique(唯一)
当定义了唯一约束后,该列值是不能重复的
基本使用:
字段名 字段类型 unique
细节说明:
- 如果没有指定 not null,则unique字段可以有多个null
- 一张表可以有多个unique字段
foreign key(外键)
用于定义主表和从表之间的关系:外键约束要定义在从表上,主表则必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为null
基本使用:
foreign key(本表字段名) refernces 主表名(主键名或unique字段名)
细节说明:
- 外键指向的表的字段,要求是primary key或者是 unique
- 表的类型是innodb,这样的表才支持外键
- 外键字段的类型要和主键字段的类型一致(长度可以不同)
- 外键字段的值,必须在主键字段中出现过,或者为null【前提是外键字段允许为null】
- 一旦建立主外键的关系,数据不能随意删除了
自增长
在某张表中,存在一个id列(整数),我们希望在记录的时候。该列从一开始,自动的增长
基本使用:
字段名 整形 auto_increment
细节说明:
- 一般来说自增长是和primary key 配合使用的
- 自增长也可以单独使用【但是需要配合一个unique】
- 自增长修饰的字段为整数型(虽然小数也可以但是非常少使用)
- 自增长默认从1开始,可以通过修改 alter table 表名 auto_increment=xxx;
MySQL索引
说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调sql,查询速度就可以提高百倍千倍