##链接---------------------------------------------------------- mysql -h主机地址 -P端口号 -u用户名 -p用户密码 #完整示例 mysql -h127.0.0.1 -P3306 -uroot -p123456 #省略（使用）默认端口号3306 mysql -h127.0.0.1 -uroot -p123456 #省略（使用）本地的MySQL服务器、默认端口号3306 mysql -uroot -p123456 #-p后面无密码，回车键后采用暗文形式输入密码 mysql -uroot -p
#没有就创建，已有同名就报错 create table users( id int, name text, age num );
#没有就创建，已有同名就跳过 create table if not exists users( id int, name text, age num );
##---------------------------------------删除表 drop table 表名;
#有就删除，没有就报错 drop table users;
#有就删除，没有就跳过 drop table if exists users;
##---------------------------------------修改表
#修改表名 rename table stu to person; rename table 原始名称 to 新的名称;
#添加字段 alter table 表名 add 新增字段名称 新增字段数据类型 [位置]; #默认添加在最后 alter table person add age int; #添加最前面 - first alter table person add score float first; #添加在name字段的后面 - after xxx alter table person add phone int after name;
#删除字段 alter table 表名 drop 字段名称; alter table person drop phone;
#修改字段的数据类型 alter table 表名 modify 需要修改的字段名称 新的数据类型 alter table person modify score double;
#修改字段的名称和数据类型 alter table 表名 change 原始字段名称 新的字段名称 新的数据类型; alter table person change age addr text;
rand(); #生成随机数 select rand() from dual; select * from stu order by rand();
round()#四舍五入 select round(3.1) from dual; select round(3.5) from dual;
ceil(); #向上取整 select ceil(3.1) from dual;
floor(); #向下取整 select floor(3.9) from dual;
truncate(); #截取小数位 select truncate(3.1234567, 2) from dual;
#3.字符串类--------------------------------------------------------- ucase(); #转换为大写 select ucase('hello world') from dual;
lcase(); #转换为小写 select lcase('HELLO WORLD') from dual;
left(); #从左边开始截取到指定的位置 select left('1234567890', 3) from dual;
right();#从右边开始截取到指定的位置 select right('1234567890', 3) from dual;
substring(); #从指定位置开始截取指定个字符 select substring('1234567890', 3, 5) from dual;
数据分组
1 2 3 4 5 6 7 8 9 10
select 分组字段 || 聚合函数 from 表名 group by 分组字段;
#查询到所有城市-有重复 select city from stu; #按照城市分组 select city from stu group by city; #统计每个城市中有多少人 select city, count(*) from stu group by city; #统计每个城市中有哪些人（把name拼接成字符串） select city, group_concat(name) from stu group by city;
select * from stu where city='北京'; select * from stu having city='北京';
#例：查询平均年龄大于40的城市--------------------------------
#查询城市分组 select city from stu group by city; #查询城市平均年龄 select city, avg(age) from stu group by city; #给结果集的平均年龄这一列起一个好听的名字 select city, avg(age) as average from stu group by city; #查询结果集中平均年龄大于40的城市 select city, avg(age) as average from stu group by city having average>=40;
数据分页 limit
1 2 3 4
select 字段 from 表 limit 索引, 个数;
select * from stu limit 0, 3; select * from stu limit 3, 3;
查询选项
1 2 3 4 5 6 7 8
select [查询选项] 字段名称 from 表名; all 显示所有查询出来的数据[默认] distinct 去除结果集中重复的数据之后再显示
#distinct对结果集中重复的数据进行去重 #只有所有列的数据都相同才会去重 select distinct name from stu; select distinct name, score from stu;
多表查询
union
返回的结果集的表头的名称是第一张表的名称
使用union进行多表查询, 必须保证多张表查询的字段个数一致
使用union进行多表查询, 默认情况下会自动去重
使用union进行多表查询, 如果不想自动去重, 那么可以在union后面加上all #select id, name from stu union all select id, name from person;
1 2 3 4 5
#默认情况下多表查询的结果是笛卡尔集 select * from 表名1, 表名2;
#union:在纵向上将多张表的结果结合起来返回给我们 select * from 表名1 union select * from 表名2 union select * from 表名3;
#标准子查询(返回的结果只有一个) select stuId from grade where score = 100;#查询一百分数的学生ID select name from stu where stuId = 3; #查询一百分学生ID的姓名 #=》 select name from stu where stuId = (select stuId from grade where score = 100);
#非标准子查询(返回的结果有多个) select stuId from grade where score >= 60;#查询60分以上的学生ID select name from stu where stuId = 3 OR stuId = 1; #查询60分以上的学生ID的姓名（方法1） select name from stu where stuId in(3, 1);#查询60分以上的学生ID的姓名（方法2） #=》 select name from stu where stuId in(select stuId from grade where score >= 60);
#将一个查询语句查询的结果作为另一个查询的表来使用------------------------------------------------- select name, city, score from person where score >= 60;#查询person表中分数大于60的姓名、城市、分数 select name, city, score from (select name, city, score from person where score >= 60) as t; #注意点:如果要将一个查询语句查询的结果作为另一个查询的表来使用, 那么必须给子查询起一个别名
#示例一:只要超出申请的范围就会报错 create table person( id int, name1 char(2), name2 varchar(2) ); insert into person values (1, 'a', 'b'); insert into person values (1, '12', '34'); insert into person values (1, 'abc', 'def');
#MySQL中每一行存储的数据是有大小限制的, 每一行最多只能存储65534个字节.超过就会报错 #注意是一行，而不是单元格。如下为（21845+3）*3 = 65544 字节 create table person( name1 char(3), name2 varchar(21845) #在UTF8中相当于65535个字节 )charset=utf8; # 报错：Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
#创建一张表，gender字段为枚举类型 create table person( id int, gender enum('男', '女', '保密') ); #插入数据 insert into person values (1, '火'); #会报错 insert into person values (2, '男'); #不会报错，成功插入
#由于MySQL的枚举底层是使用整型实现的, 所以我们在赋值的时候除了可以赋值固定的几个值以外 #我们还可以赋值对应的整数 # select gender+0 from person; insert into person values (3, 1); #1代表男，不会报错 insert into person values (4, 4); #会报错
#创建表 create table person( id int, hobby set('篮球','足球','高尔夫球','足浴') );
#插入数据 insert into person values (1, '篮球,足球,高尔夫球'); #不会报错 insert into person values (1, '橄榄球'); #会报错
#集合类型的底层也是使用整型实现的 # select hobby+0 from person; insert into person values (2, '篮球'); #1 insert into person values (3, '足球'); #2 insert into person values (4, '高尔夫球'); #4 insert into person values (5, '足浴'); #8 insert into person values (6, '篮球,足球,高尔夫球'); #1+2+4=7 #MySQL的集合类型是按照2(n)的方式来实现的 #篮球在set(..)中的索引是0，依次类推 #篮球 2(0) = 1 #足球 2(1) = 2 #高尔夫球 2(2) = 4 #足浴 2(3) = 8
布尔类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14
create table person( id int, vip boolean ); #在表中，true显示为1，false显示为0 insert into person values (1, '男'); #会报错 insert into person values (1, true); #不会报错 insert into person values (2, false); #不会报错
#MySQL中的布尔类型也是使用整型来实现的, 0就表示假, 1就表示真 #底层的本质是因为MySQL是使用C/C++来实现的, 所以就是'非零即真' insert into person values (3, 1); #不会报错 insert into person values (4, 0); #不会报错 insert into person values (5, 2); #不会报错，显示2
日期类型
在存储时间的时候, 需要用单引号将时间括起来。否则会报错
DATE 3字节 YYYY-MM-DD 日期值
TIME 3字节 HH:MM:SS 时间值或持续时间
DATETIME 8字节 YYYY-MM-DD HH:MM:SS 混合日期和时间值
1 2 3 4 5 6 7
create table person( id int, filed1 DATE, filed2 TIME, filed3 DATETIME ); insert into person values (1, '2020-02-02', '14:18:23', '2020-02-02 14:18:23');
数据完整性
什么是数据的完整性?
保证保存到数据库中的数据都是正确的。
如何保证数据完整性?
数据的完整性可以分为三类: 实体完整性、域完整性、参照完整性
无论是哪一种完整性都是在创建表时给表添加约束即可
实体完整性
什么是实体?
表中的一行数据就是一个实体（entity）
如何保证实体完整性?
保证实体完整性就是保证每一行数据的唯一性
实体完整性的约束类型
主键约束（primary key）
唯一约束(unique)
自动增长列(auto_increment)
主键约束 primary key
主键用于唯一标识表中的每一条数据, 和现实生活中的身份证很像。主键的特征:
如果将某一个字段设置成了主键, 那么这个字段的取值就不能重复了
如果将某一个字段设置成了主键, 那么这个字段的取值就不能是null了
一张表中只能有一个主键, 不能出现多个主键
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
#创建表 create table person( id int primary key, #方法一 name varchar(20) );
create table person3( id int, name varchar(20), primary key(id) #方法二 );
#插入数据 insert into person values (1, '百度'); #可 insert into person values (1, '淘宝'); #报错 insert into person values (2, '百度'); #可
#修改成主键约束------------------------------------------ alter table 表名 add primary key(字段); alter table person add primary key(id);
#修改成唯一约束------------------------------------------ alter table 表名 add unique(字段); alter table person add unique(name);
#修改成自动增长约束--------------------------------------- alter table 表名 modify 字段名称 数据类型 auto_increment; #必须先把字段设置为主键约束 alter table person add primary key(id); #再修改成自动增长 alter table person modify id int auto_increment;
#删除外键------------------------------------------------------------------------ alter table 从表名称 drop foreign key 外键名称; alter table grade drop foreign key grade_ibfk_1;
#示例：创建数据--------------------------------------------------------- create table bank( id int unsigned auto_increment primary key, cardid varchar(4), name varchar(20), money int ); insert into bank values (null, '1001', 'zs', 1000), (null, '1002', 'ls', 1000);
#使用事务--------------------------------------------------------- start transaction; #开启事务 update bank set money=money-1000 where cardid='1002'; update bank set money=money+1000 where cardid='1001'; commit; #任务成功, 提交开启事务之后所有操作
事务回滚点
savepoint 回滚点名称
rollback to 回滚点名称
1 2 3 4 5 6 7
start transaction;#开启事务 insert into bank values (null, '1003', '333', 33333); savepoint abc; #设置回滚点 insert into bank values (null, '1003', '444', 44444); savepoint def; #设置回滚点 insert into bank values (null, '1003', '555', 55555); rollback to abc; #回到回滚点
#设置隔离级别---------------------------------------------- #全局的 set global transaction isolation level 级别名称; #当前会话 set session transaction isolation level 级别名称;
create [algorithm={merge||temptable||undefined}] view 视图名称 as select 语句 [with check option]; #例： CREATE VIEW person_view as SELECT name, city FROM person
#视图数据操作-------------------------------------------------------------------------------- SELECT name, city FROM person; #查询person中的name和city CREATE VIEW person_view as SELECT name, city FROM person; #创建视图 SELECT * FROM person_view; #查询视图 INSERT INTO person_view values ('it666', '武汉'); #向视图中增加数据 UPDATE person_view set city='香港' WHERE name='it666'; #修改视图数据 DELETE FROM person_view WHERE name='it666'; #删除视图数据
#修改视图内容-------------------------------------------------------------------------------- alter view 视图名称 as select 语句; alter view person_view as select name, score from person; #将这张视图重新显示为name和score
#删除视图----------------------------------------------------------------------------------- drop view [if exists] 视图名;
SELECT name, city, score FROM person WHERE score >= 60; CREATE VIEW person_view AS SELECT name, city, score FROM person WHERE score >= 60 with check option; INSERT INTO person_view values('it666', '台湾', 33); #报错, 不符合scroe>=60条件 UPDATE person_view set score=55 WHERE name='ww'; #报错, 不符合scroe>=60条件
SELECT city, avg(score) as avgScore FROM person GROUP BY city; CREATE VIEW person_view AS SELECT city, avg(score) as avgScore FROM person GROUP BY city; SELECT * FROM person_view; UPDATE person_view set avgScore=11 WHERE city='北京'; #报错
更新限制 聚合函数 DISTINCT关键字 GROUP BY子句 HAVING子句 UNION运算符 FROM子句包含多张表 SELECT语句中应用了不可更新的形势图
#传统mysql处理流程--------------------------------------------------------- 1， 在客户端准备sql语句 select * from stu where id=1; select * from stu where id=2; 2， 发送sql语句到MySQL服务器 3， MySQL服务器对sql语句进行解析(词法,语法), 然后编译, 然后执行该sql语句 4， 服务器将执行结果返回给客户端 弊端: - 哪怕多次传递的语句大部分内容都是相同的, 每次还是要重复传递 - 哪怕语句是相同的, 每次执行之前还是要先解析、编译之后才能执行
#预处理的处理流程--------------------------------------------------------- 1， 在客户端准备预处理sql语句 prepare 预处理名称 from 'sql语句'; prepare stmt from 'select * from stu where id=?;'; 2， 发送预处理sql语句到MySQL服务器 3. MySQL服务器对预处理sql语句进行解析(词法,语法), 但不会执行 4. 在客户端准备相关数据 set @id=1; 5. MySQL服务器对数据和预处理sql编译, 然后执行该sql语句 execute stmt using @id; 6. 服务器将执行结果返回给客户端 优点: - 只对sql语句进行了一次解析 - 重复内容大大减少(网络传输更快)
#演示 prepare stmt from 'select * from stu where id=?;'; set @id=1; execute stmt using @id; set @id=2; execute stmt using @id;
