数据库
1、注释
- –:单行注释(快捷键Ctrl+/)
- /* */:多行注释
2、常用数据类型
3、数据库中的元素
- 数据库------database
- 表-----table label: LOOP statement_list IF exit_condition THEN LEAVE label; END IF; END LOOP label;
- 字段(列)-----filed
- 记录(行)-----record
4、创建表
- 语法:creat table 表名(字段名 字段类型,字段名 字段类型)
创建一个字段:
创建两个字段:
创建三个字段:
5、插入数据
-
语法:insert into 表明 values (值,值,值)
所有字段设置值,值的顺序与表中字段的顺序对应
-
语法:insert into 表明 (字段1,字段2…)values (值1,值2)
部分字段设置值,值的顺序与给定的字段顺序对应,指定字段插入
6、插入多条记录
-
多条insert语句,用分号隔开
-
一条insert插入多条记录
语法:insert into 表明 values (值,值),(值,值),(值,值);
-- 表c插入三条记录,写三条insert语句,语句之间用分号隔开 INSERT into c VALUES (5,'周瑜', 50); INSERT into c (id ,name) VALUES (6,'dff'); INSERT into c (name) VALUES ('周瑜');
-
一条insert,指定字段插入多条记录
语法: insert into 表名 (字段名1,字段名2)values (值1,值2),(值1,值2),(值1,值2);
7、select 简单查询
-
查询所有字段
-
语法: select * from 表名
(1)查询表的所有字段
-- 查询表c的所有字段 select * from c
(2)指定字段名查询
语法:select 字段名, 字段名 from 表名;
-- 查询表c的ID字段 select id from c -- 查询表c的ID和name字段 select id, name from c; -- 查询表c的所有字段,但顺序可以自定义 select name, id, age from c;
8、update 修改数据
- 语法:update 表明 set 字段 = 值,字段 = 值 where 条件
- 如果没有where条件代表修改表中的所有记录
-- 例1、修改表c,所有人的年龄(age 字段)改为50
UPDATE c set age = 50;
- 修改带有条件的update语句
-- 修改表c,id为3的记录,姓名(name 字段)改为‘狄仁杰’,年龄(age 字段)改为 20
UPDATE c set name = '狄仁杰', age = 20 WHERE id = 3;
-
另外的例子
-- 修改name为张飞的记录为李白 UPDATE c set name = '李白' WHERE name = '张飞';
9、delete 删除表中的数据
- 语法:delete from 表名 where 条件
-- 删除表c当中ID为6的记录
DELETE FROM c WHERE id = 6;
10、truncate删除表中的记录
- truncate table 表名; (不能设定条件)
-- 删除表c中的所有数据
truncate table c;
11、delete和truncate的区别
- 在速度上,truncate>delete
- 如果想删除部分数据用delete,要带上where子句
- 如果想保留表而将表中所有数据删除,自增长字段恢复从1开始,用truncate
12、drop table 删除表
- 语法:drop table 表名;
-- 删除表a
DROP TABLE a;
-- 如果表a存在删除表a,如果不存在,不做任何操作
drop TABLE if EXISTS a;
13、字段的约束
- 主键(primary key):值不能重复,auto_increment代表值自动增长;
- 非空(not null):此字段不允许填写空值;
- 唯一(unique):此字段的值不允许 重复;
- 默认值(default):当不填写此值时会使用默认值,如果填写时以填写为准;
(1)创建带约束条件的语法格式
create table 表名(
字段名 数据类型 约束,
字段名 数据类型 约束,
...
);
(2)主键与自增长
- 带有primary key 的字段,值不能重复;
- auto_increment 为自增长;
- 语法
create table 表名(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 约束,
...
);
例子:
-- 创建表d,字段要求如下:
-- ID : 数据类型为int unsigned (无符号整数),primary key ,auto_increment
-- name 姓名:数据类型为varchar(字符串)长度为10;年龄age:数据类型为int;
CREATE TABLE d (
id int UNSIGNED PRIMARY KEY auto_increment,
name VARCHAR (10),
age INT
);
INSERT into d (name , age)VALUES ('张飞',33);
-- 插入指定ID的值
INSERT INTO d (id ,name ,age) VALUES(6,'关羽', 55);
-- 不指定ID的值
INSERT into d (name , age)VALUES ('天司',33);
SELECT * from d;
-- 如果不指定字段,主键自增长字段的可以用占位符,0或null
INSERT into d VALUES (0,'张三',30);
INSERT into d VALUES (null,'李四',26);
SELECT * from d;
(3)非空 not null
- 这个字段必须有值,若没有值则会报错
-- 创建表e,字段要求如下:
-- ID : 数据类型为int unsigned (无符号整数),primary key ,auto_increment
-- name 姓名:数据类型为varchar(字符串)长度为10,not null (非空);年龄age:数据类型为int;
DROP TABLE e;
CREATE TABLE e (
id int UNSIGNED,
name VARCHAR (10) not null,
age int
);
INSERT into e VALUES (1,'wsz',20);
INSERT into e (id , age ) VALUES (1,20);
SELECT * from e;
INSERT into e (id , age ) VALUES (1,20)
> 1364 - Field 'name' doesn't have a default value
> 时间: 0.001s
(4)唯一 unique
- 该字段的值不能重复,出现重复报错;
-- 创建表f,字段要求如下:
-- ID : 数据类型为int (整数),primary key ,
-- name 姓名:数据类型为varchar(字符串)长度为10,UNIQUE(唯一);年龄age:数据类型为int;
CREATE TABLE f (
id int,
name VARCHAR (10) UNIQUE,
age int
);
INSERT into f VALUES (1,'张三',20);
INSERT into f VALUES (2,'张三',30);
SELECT * from f;
INSERT into f VALUES (2,'张三',30)
> 1062 - Duplicate entry '张三' for key 'f.name'
> 时间: 0.002s
(5)默认值 default
- 当一个字段有默认值约束,插入数据时,如果指定了值,那么默认值无效,否则会使用默认值填充
-- 创建表g,字段要求如下:
-- ID : 数据类型为int (整数);
-- name 姓名:数据类型为varchar(字符串)长度为10,UNIQUE(唯一);年龄age:数据类型为int,DELETE (默认值)30;
CREATE TABLE g (
id int,
name VARCHAR (10),
age int DEFAULT 30
);
INSERT into g VALUES (1,'张三',30);
--插入时不指定age的值
INSERT into g (id , name) VALUES (2,'李四');
SELECT * FROM g;
14、字段的别名
- 通过 字段名 as 别名 的语法,可以给字段起一个别名,别名可以是中文
- as 可以省略
- 字段名 as 别名 和字段名 别名 结果是一样的
-- 通过as给字段起一个别名
SELECT card as 身份证,name as 姓名,sex as 性别 from students;
15、表的别名
-- 通过as给表student起一个别名
SELECT * from students as stu;
-- 可以省略as
SELECT * from students stu;
16、 distinct过滤重复记录
- 通过 select distinct 字段名,字段名 from 表名 过滤select查询结果中的重复记录
-- 去掉字段的重复记录
SELECT DISTINCT sex from students;
SELECT DISTINCT sex,class from students;
17、where子句
(1)条件查询
-
where 后面跟一个条件,实现有选择的查询
-
select * from 表名 where 条件
-- 查询students表中学号为001的记录
SELECT * from students WHERE studentNo = 001;
-- 查询students表中年龄age 为30 的姓名name,班级class
select name , class from students where age = 30;
- select 后面的 * 或者字段名,决定了返回什么样的字段(列);
- select 中where子句,决定了返回什么样的记录(行);
(2)比较运算符
- where后面可以支持多种运算符,进行条件的处理:比较运算;逻辑运算;模糊查询;范围查询;空判断。
- 等于:=;大于:>;小于:<;大于等于:>=;小于等于:<=;不等于:!=或<>
-- 查询表中30岁及以下的学生记录
SELECT * from students where age <= 30;
-- 查询家乡不在北京的记录
SELECT * from students WHERE hometown <> '北京';
(3)逻辑运算符
- 与(and):条件1 and 条件2 两个条件必须同时满足
-- 查询年龄小于30,并且性别为女的同学记录
SELECT * from students WHERE age < 30 and sex = '女';
- 或(or):条件1 or 条件2 两个条件只要满足一个即可;
-- 查询性别为女或者班级为1班的同学记录
SELECT * from students WHERE sex = '女' or class = '一班';
- 非(not):not 条件 not只有一个条件,如果条件为满足,not后变为不满足,反之,not后变为满足;
-- 查询学生家乡非天津的学生记录
SELECT * from students WHERE not hometown = '天津';
18、模糊查询 like
- % 表示任意多个任意字符;
- _ 表示一个任意字符;
-- 查询name姓名中姓孙的同学的记录
SELECT * from students where name like '孙%';
-- 查询name姓名中姓名以孙开头后面只有一个字的同学的记录
SELECT * from students where name like '孙_';
19、范围查询
- in表示在一个连续的范围内查找
- 语法:in (值,值,值)
-- 查询家乡是北京或上海或广东的学生记录
SELECT * FROM students WHERE hometown = '北京' or hometown = '上海' or hometown = '广东';
SELECT * FROM students WHERE hometown in ('北京','上海','广东');
- between…and…表示在一个连续的范围内查找 常用于数字和字母,包含开始值和结束值;
-- 查找age年龄为25至30之间的学生记录
SELECT * FROM students WHERE age BETWEEN 25 and 30;
20、空判断
注意:null和‘’是不同的;null代表什么都没有,‘’代表长度为0的字符串;null在SQL里代表空,不能用比较运算符的来判断
- 语法:is null 和 is not null
- 不能用字段名= null和字段名 !=null这种写法
-- 查询card身份证为null的学生记录
SELECT * FROM students WHERE card is null;
-- 查询card身份证为非null的学生记录
SELECT * FROM students WHERE card is not null;
21、where在update和delete中同样有效
-- 修改age为20 ,并且name为孙尚香的学生的class为2班
UPDATE students set class = '2班' WHERE age = 25 and name = '孙尚香';
-- 删除class为1班,并且age大于30的学生记录
DELETE FROM students WHERE class = '1班' and age > 30 ;
22、排序 order by
- 语法:order by 字段名 [asc(升序,可以省略)/desc(降序)]
- 排序依据的字段的优先级由高到低,第一个最高依次降低;
-- 查询所以学生记录,按照年龄从小到大排序
SELECT * FROM students ORDER BY age asc;
-- 查询所以学生记录,按照年龄从大到小排序
SELECT * FROM students ORDER BY age DESC;
-- 查询所有学生记录,按age年龄从大到小排序,年龄相同时,再按照学号从小到大排
SELECT * FROM students ORDER BY age DESC, studentNo;
-
当select语句出现了where和order by
语法:select * from 表名 where 条件 order by 字段1,字段2;
注:一定要把where写在order by之前
-- 查询所有男生的记录,按照班级从小到大排序,班级相同时,再按学号从大到小排序
SELECT * from students WHERE sex = '男' ORDER BY class, studentNo DESC;
23、聚合函数
注:聚合函数不能再where 后面的条件中使用
-- 查询年龄最大的同学的名字
SELECT name FROM students WHERE age = max(age);
SELECT name FROM students WHERE age = max(age)
> 1111 - Invalid use of group function
> 时间: 0s
(1)count 求select的总记录数
- count (*) 表示计算总记录数,括号中写 * 和字段名,结果是相同的
-- 查询学生总数(查询students表中有多少记录)
select count(*) from students;
select count(name) from students;
select count(DISTINCT class) from students;
-- 查询女同学的数量
SELECT count(*) from students where sex = '女';
(2)max 最大值
- 语法: max (字段)表示指定字段的最大值
-- 查询最大年龄
SELECT max(age) FROM students;
-- 查询女生中最大的年龄
SELECT max(age) FROM students WHERE sex = '女';
(3)min 最小值
- 语法: min (字段)表示指定字段的最小值
-- 查询最小年龄
SELECT min(age) FROM students;
-- 查询女生中最大的年龄
SELECT min(age) FROM students WHERE sex = '女';
(4)sum 总和
- 语法: sum (字段)表示求此字段的和
-- 查询年龄的总和
SELECT sum(age) FROM students;
-- 查询女生的年龄总和
SELECT sum(age) FROM students WHERE sex = '女';
(5)avg 平均值
-
语法: avg (字段)表示求此字段的平均值
avg的字段中,如果有null时,null不作为分母计算平均值
-- 查询学生的平均年龄
SELECT avg(age) FROM students;
24、数据分组
- 按照字段分组,表示此字段相同的数据会被放到一个组中
- 分组的目的是配合聚合函数,聚合函数会对每一组的数据分别进行统计
- 语法:select 字段1,字段2,聚合函数…from 表名 where 条件 group by 字段1,字段2…
-- 分别查询学生中的男生和女生的数量
SELECT sex,count(*) from students GROUP BY sex;
-- 查询各个年龄段的同学的数量
SELECT age,count(*) from students GROUP BY age;
- where与group by
-- 查询1班不同性别的同学的数量
SELECT sex,count(*) from students where class = '1班' GROUP BY sex;
-
where和group by和order by 的顺序
select count()from 表名 where 条件 group by 字段 order by asc/desc*
-- 统计各个班级学生总数,平均年龄,最大年龄,最小年龄;但不统计3班,统计结果按班级名称从大到小排序
SELECT class,count(*), avg(age),MAX(age),MIN(age) FROM students WHERE class <> '3班' GROUP BY class ORDER BY class DESC;
25、分组聚合之后的数据筛选
- where 先筛选符合条件的记录,然后再聚合统计
- having 先分组聚合,在统计的结果中筛选,having总是出现在group by 之后
- having能够使用聚合函数,where后面不能使用聚合函数
- where是对表的原始数据进行筛选,having是对group by之后已经分过组的数据进行筛选
-- 用where查询男生总数
SELECT count(*) from students WHERE sex = '男';
-- 用group by 和 having 查询男生总数
SELECT sex,count(*) from students GROUP BY sex HAVING sex = '男';
-- 求班级人数大于3人的班级
SELECT class,count(*) from students GROUP BY class HAVING COUNT(*) > 3;
26、数据分页
- 语法:limit 开始行,获取行数;
- limit 总是出现在select语句的最后;
- start代表开始行号,行号从0开始;
- count表示要获取的行数;
- 省略start,默认从0开始,从第一行开始;
- 当同时出现where,order by ,limit是limit总是写在最后;
-- 查询前三行同学的记录
SELECT * from students LIMIT 0,3;
-- 查询从第四行开始的三条记录
SELECT * from students LIMIT 3,3;
-- 查询年龄最大的同学的那条记录
SELECT * from students ORDER BY age desc LIMIT 1;
-- 查询年龄最小的女同学的信息
SELECT * from students WHERE sex = '女' ORDER BY age LIMIT 1;
27、数据分页显示
- m 代表每页显示多少条记录
- n,表示n页
- 公式:limit (n-1)*m,m
-- 每页显示4条记录第三页显示的记录
SELECT * FROM students LIMIT 8,4;
-- 每页显示34条记录第2页显示的记录
SELECT * FROM students LIMIT 4,4;
- 已知每页条数,求一张表需要几页显示完
- 第一步:求总页数
- 第二步:总页数/每页行数
- 第三步:如果结果是整数,则为页数,否则结果+1;
-- 第一页
SELECT * FROM students LIMIT 5;
-- 第二页
SELECT * FROM students LIMIT 5,5;
-- 第三页
SELECT * FROM students LIMIT 10,5;
28、连接查询
(1)基本概念
-
当查询结果来源于多张表时,需要将多张表连接成一个大的数据集,在选择合适的结果返回;
-
连接查询可以通过链接运算符(连接条件)可以实现多个表查询;
-
内连接:查询的结果只显示两个表中满足连接条件的部分;
-
左连接:查询结果为两个表匹配到的数据加左表持有的数据,对于右表中不存在的数据使用null填充
-
右连接:查询结果为两个表匹配到的数据加右表持有的数据,对于左表中不存在的数据使用null填充
(2)内连接
-
语法一:
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段;
内连接最重要的是找对两张表要关联的字段;
-- 内连接
SELECT * from a INNER JOIN b on a.id = b.id;
- students表和scores表内连接查询结果
- 隐式内连接
- 语法:select * from 表1,表2 where 表1.字段 = 表2.字段
SELECT * FROM students INNER JOIN scores on students.studentNo = scores.studentNo;
-- 隐式内链接
SELECT * from students, scores WHERE students.studentNo = scores.studentNo;
- 内连接查询显示指定的字段
-- students 表与scores表内连接,只显示name 课程号 成绩
SELECT name, courseNo,score from students INNER JOIN scores on students.studentNo = scores.studentNo;
- 表的别名的使用
-- students 表与scores表内连接,只显示name 课程号 成绩
-- 表的别名
SELECT name, courseNo,score from students st INNER JOIN scores sc on st.studentNo = sc.studentNo;
-
带有where的内连接
语法:select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 where 条件;
-- 查询王昭君的信息,要求只显示姓名,课程号,成绩
SELECT name,courseNo,score from students s1 INNER JOIN scores s2 on s1.studentNo = s2.studentNo
where s1.name = '王昭君';
-- 查询姓名为王昭君,并且成绩小于90的信息,要求值显示姓名、成绩
SELECT name,score from students s1
INNER JOIN scores s2 on s1.studentNo = s2.studentNo
where s1.name = '王昭君' and s2.score < 90;
- 多表的内连接
-- 查询学生的信息和成绩以及成绩对应的课程名称
SELECT * FROM students S1
INNER JOIN scores s2 on s1.studentNo = s2.studentNo
INNER JOIN courses c1 on s2.courseNo = c1.courseNo;
(3)左连接
- 语法:select * from 表1 left join 表2 on 表1.字段 = 表2.字段;
-- 查询所有学生的信息以及成绩,包括没有成绩的学生
SELECT * from students s1 LEFT JOIN scores s2 on s1.studentNo = s2.studentNo;
(4)右连接
- 语法:select * from 表1 right join 表2 on 表1.字段 = 表2.字段;
-- 查询所有的课程信息,包括没有成绩的课程
SELECT * from scores s1 RIGHT JOIN courses c1 on s1.courseNo = c1.courseNo;
29、多表联合查询,同名字段的处理方式
- 如果一条select要用到多个表,表中有同名字段,就需要表名.字段名 加以区分
30、写SQL三步法
- 搭框架:基本的select语句框架搭建起来,如果有多表,把响应的多表也联合起来
- 看条件:决定where后面的具体条件
- 显示的字段:select后面到底要显示什么字段
31、带有order by 排序的内连接查询
-- 查询成绩最高的男生信息,要求只显示姓名、成绩、课程名
SELECT name 姓名 , score 成绩, coursename 课程名 from students s1
INNER JOIN scores s2 on s1.studentNo = s2.studentNo
INNER JOIN courses c1 on s2.courseNo = c1.courseNo
WHERE sex = '男' ORDER BY score DESC LIMIT 1;
32、自关联
-- 查询一共有多少个省
SELECT COUNT(*) from areas WHERE pid is null;
-- 查询一共有多少市
SELECT COUNT(*) from areas WHERE pid is not null;
- 自关联是同一张表做连接查询
- 自关联下一定要找到同一张表的可关联的不同字段
-- 查询广东省所有的城市
SELECT * from areas a1 INNER JOIN areas a2 on a1.id = a2.pid WHERE a1.name = '广东省';
33、子查询
- 子查询是嵌入到主查询中;
- 子查询是辅助主查询的,要么充当条件,要么充当数据源
- 子查询是可以独立存在的语句,是一条完整的select语句
- 主查询不能独立运行,要依赖子查询的结果
-- 查询大于平均年龄的学生记录
SELECT AVG(age) from students;
SELECT * from students WHERE age > 30.1667;
-- 用子查询实现
SELECT * from students WHERE age > (SELECT AVG(age) from students);
- 标量子查询 ----- 子查询返回结果只有一行,一列
-- 查询30岁学生的成绩
SELECT studentNo from students WHERE age = 30;
SELECT * from scores WHERE studentNo in (SELECT studentNo from students WHERE age = 30);v
- 列子查询 ----- 子查询返回一列多行;
-- 查询30岁学生的成绩
-- 1、查询30岁学生的studentNo
SELECT studentNo from students WHERE age = 30;
SELECT * from scores WHERE studentNo in (SELECT studentNo from students WHERE age = 30);
- 表级子查询 ------ 子查询返回结果为多行多列
-- 用子查询查询所有女生的信息和成绩
SELECT * from (SELECT * FROM students WHERE sex = '女') stu INNER JOIN scores s2 on stu.studentNo = s2.studentNo;
SELECT * FROM students WHERE sex = '女';
34、MySQL内置函数
(1)字符串函数
-
拼接字符串 concat (str1,str2…)
参数可以是数字,也可以是字符串
把所有的参数连成一个完整的字符串
-- 例 1:把 12,34,’ab’拼接为一个字符串’1234ab’
SELECT CONCAT( 12,34,'ab');
-
包含字符个数 length(参数):返回字符串字符的个数
一个utf8格式的汉字length返回3
-- 计算字符串’abc’的长度 SELECT LENGTH('abc'); -- 计算字符串’我和你’的长度 SELECT LENGTH('我和你');
-
MySQL内置函数可以在where条件中使用
-- 查询表 students 中 name 长度等于 9(三个 utf8 格式的汉字)的学生信息
SELECT * from students WHERE LENGTH(name) = 9;
-
截取字符串 left/right/substring
语法:left (字符串,len) 从字符串左侧截取len长度的字符
right (字符串,len) 从字符串右侧截取len长度的字符
注:汉字也属于一个字符
-- 截取字符串’我和你 abc’的右端 3 个字符
SELECT LEFT('我和你abc',3);
语法:substring(str,pos,len) 返回字符串 str 的位置 pos 起 len 个字符,pos 从 1 开始计数;
从中间开始截取;
-- 截取字符串’我和你 abc’从第 2 个字符开始的的 3 个字符
SELECT SUBSTRING( '我和你ABC',2,3);
-- 截取 students 表中所有学生的姓
SELECT LEFT(name,1) from students;
-
去除字符串空格
(1)左侧空格:ltrim (str)返回删除左侧空格的字符串 str;
-- 去除字符串' abcd '左侧空格 SELECT LTRIM(' abcd');
(2)右侧空格:rtrim (str) 返回删除右侧空格的字符串 str;
-- 去除字符串' abcd '右侧空格 SELECT RTRIM('abd ');
(3)去除两边的空格:trim(str)返回删除左右两侧空格的字符串 str;
(2)数学函数
- 求四舍五入值求四舍五入值 round(n,d) n 表示原数,d 表示小数位置,默认为 0
-- 1.653 四舍五入,保留整数位
SELECT ROUND(1.653,2);
-- 查询 students 表中学生的平均年龄,并四舍五入
SELECT round(avg(age),2) FROM students ;
-
随机数:rand() 值为0-1.0的浮点数
order by rand() 随机排序
SELECT RAND();
-- 从学生表中随机抽出一名学生
SELECT name from students ORDER BY rand() LIMIT 1;
(3)日期时间函数
- 当前日期 current_date()
- 当前时间 current_time()
- 当前日期时间 now()
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT NOW();
35、存储过程
-
存储过程 PROCEDURE,也翻译为存储程序,是一条或者多条 SQL 语句的集合;
-
创建存储过程
语法:create procedure 存储过程名称(参数列表)
begin
sql 语句
end
-- 创建查询过程 stu(),查询students表的所有信息
CREATE PROCEDURE stu()
BEGIN
SELECT * from students;
END;
-- 调用存储过程stu
call stu();
-- 删除存储过程,删除的时候不用写名字后面的()
drop PROCEDURE stu;
drop PROCEDURE if EXISTS stu; -- 避免报错
36、视图
- 语法:create view 视图名称 as select 语句;
- 视图就是对select语句的封装
- 视图可以看做一张只读的表,针对视图只能用select,不能用delete和update
-- 创建视图,名叫 stu_nan,查询所有男生信息
CREATE VIEW stu_nan as SELECT * FROM students WHERE sex = '男';
-- 使用视图
SELECT * FROM stu_nan WHERE age >30;
SELECT * FROM stu_nan s1 INNER JOIN scores s2 on s1.studentNo = s2.studentNo;
-- 删除视图
DROP VIEW stu;
DROP VIEW if EXISTS stu;
37、事务
- 事务是多条更改数据操作的SQL语句集合;
- 一个集合数据有一致性,要么都失败,要么都成功;
- 开启事务:begin;
- 回滚事务:rollback;
- 提交事务:commit;
- 没有写begin的代表没有事务,操作都是实时生效的;
回滚事务的操作:
-- 开启事务,删除 students 表中 studentNo 为 001 的记录,
-- 同时删除 scores表中 studentNo 为 001 的记录, 回滚事务,两个表的删除同时放弃
begin;
DELETE FROM students WHERE studentNo = '002';
DELETE from scores WHERE studentNo = '002';
-- 回滚事务放弃更改
ROLLBACK;
SELECT * FROM students;
SELECT * from scores;
如果开始一个事务,执行了begin之后,没有rollback也没有commit,中间系统出现问题,默认会执行rollback
提交事务的操作:
-- 开启事务,删除 students 表中 studentNo 为 001 的记录,
-- 同时删除 scores表中 studentNo 为 001 的记录, 回滚事务,两个表的删除同时放弃
begin;
DELETE FROM students WHERE studentNo = '002';
DELETE from scores WHERE studentNo = '002';
-- 提交事务,一旦提交删除任务同时生效
COMMIT;
SELECT * FROM students;
SELECT * from scores;
38、索引
- index
给表建立索引,目的是加快select查询的速度
如果一个表记录很少,几十条或几百条不用索引
当表的记录达到一定数量,索引可以提高查找速度
-
创建索引
语法:create index 索引名称 on 表名(字段名称(长度));
-- 为表students的age字段创建索引,名为age_index CREATE INDEX age_index on students (age); -- 为表 students 的 name 字段创建索引,名为 name_index CREATE INDEX name_index on students (name (10));
-
调用索引不需要写调用索引的语句,只要where条件后面用到的字段建立了索引,那么系统就会自动调用
-- 这里会自动调用age_index
SELECT * FROM students WHERE age = 30;
-- 自动调用name_index
SELECT * FROM students WHERE name = '李白';
-- 不会调用任何索引,sex字段没有索引
SELECT * FROM students WHERE sex = '女';
-
查看索引
语法:show index from 表名;
系统会自动给主键primary key建立索引
SHOW INDEX FROM students;
-
删除索引
语法:drop index 索引名称 on表名;
drop INDEX age_index on student;
drop INDEX name_index on students ;
-
索引的优缺点
优点:索引大大提高了 SELECT 语句的查询速度;
缺点:虽然索引提高了查询速度,同时却会降低更新表的速度,例如对表进行INSERT、UPDATE 和
DELETE 操作。因为更新表时,不仅要保存数据,还要保存索引文件;
-
项目中80%以上是select语句
-
实际应用中, 执行 SELECT 语句的次数远远大于执行 INSERT、UPDATE 和 DELETE 语句的次数, 甚至
可以占到 80%~90%, 所以为表建立索引是必要的。
-
39、MySQL命令行
-
mysql -h mysql服务器的地址 -u 用户名 -p
-h如果是使用本机的mysql,-h可以省略;
40、mysql登陆之后的常用命令
-
show databases 显示系统所有的数据库
-
use 数据库名 使用指定的一个数据库
-
show tables 选择了数据库以后就可以查看数据库有多少表
-
如果命令行默认字符集与数据库默认字符集不同
在Windows中是gdk,那么输入 set names gdk 告诉mysql客户端用的字符集是gdk
-
在命令行中每条SQL语句用‘;’结尾
-
可以desc 表名 查看一个表的字段结构
desc students
查看students每个字段的定义;
-
创建数据库
语法:create database 数据库名 default charset [默认字符集];
-
增加新用户:
语法:grant all on . to test@‘localhost’ Identified by ‘123456’ with grant option;
系统会自动给主键primary key建立索引
SHOW INDEX FROM students;
-
删除索引
语法:drop index 索引名称 on表名;
drop INDEX age_index on student;
drop INDEX name_index on students ;
-
索引的优缺点
优点:索引大大提高了 SELECT 语句的查询速度;
缺点:虽然索引提高了查询速度,同时却会降低更新表的速度,例如对表进行INSERT、UPDATE 和
DELETE 操作。因为更新表时,不仅要保存数据,还要保存索引文件;
-
项目中80%以上是select语句
-
实际应用中, 执行 SELECT 语句的次数远远大于执行 INSERT、UPDATE 和 DELETE 语句的次数, 甚至
可以占到 80%~90%, 所以为表建立索引是必要的。
-
39、MySQL命令行
-
mysql -h mysql服务器的地址 -u 用户名 -p
-h如果是使用本机的mysql,-h可以省略;
40、mysql登陆之后的常用命令
-
show databases 显示系统所有的数据库
-
use 数据库名 使用指定的一个数据库
-
show tables 选择了数据库以后就可以查看数据库有多少表
-
如果命令行默认字符集与数据库默认字符集不同
在Windows中是gdk,那么输入 set names gdk 告诉mysql客户端用的字符集是gdk
-
在命令行中每条SQL语句用‘;’结尾
-
可以desc 表名 查看一个表的字段结构
desc students
查看students每个字段的定义;
-
创建数据库
语法:create database 数据库名 default charset [默认字符集];
[外链图片转存中…(img-1PKiaO1D-1710049257363)]
-
增加新用户:
语法:grant all on . to test@‘localhost’ Identified by ‘123456’ with grant option;