这两天系统的复习了一下mysql语法和基本知识,本文为阅读mysql必知必会的笔记,作为学习过程的记录,属于比较零散随意的笔记。
1. 查询语句
select col_name from table;
Select a, b from table where a in (1,2) order by b desc limit 1;
select a, b from table where b between 1 and 7;
…. b in (1,2,3)
2. 通配符
… where a like “prefix%”
… where a like “prefix_” 匹配单个字符,相当于.
…. where a is NULL
3.正则:
select … from table where a regexp ‘1000’ // 包含文本1000的行
Select … from table where a regexp ‘.000’ //.匹配一个字符
select … from table where a regexp ‘1000|2000’ // 或
select … from table where a regexp ‘[12]000’
select … from table where a regexp ‘[^12]1000’
select … from table where a regexp ‘\.’ //反义字符
select … from table where a regexp ‘[1-9]{6} //连续出现6个数字 通配符?0个或1个
select … from table where a regexp “^[0-9.]” //以数字或小数点开头
4. 分组 group by, having过滤分组
5. 计算:
文本拼接:
select Concat(RTrim(col1), LTrim(col2)) from table
select Concat(col1, “(“, col2, “)”) as new_col from table
文本处理函数 Left(), SubString(), Length(),
日期处理函数
Select … from table where Date(date_col) = “2015-1-1”
select … from table where Date(date_col) between “2015-1-1” and “2015-12-31”
select … from table where Year(date_col) = 2015
Time(), Month()
数值处理函数
Abs(), Cos(), Rand(), Sqrt(), Pi(), Mod()
6. 聚集函数
SUM(), COUNT(), AVG(), MAX(), MIN()
select university, count(*) as num from table group by university // 统计每个学校的人数
select university, count(*) as num from table group by university having count(*) >= 10 // having 过滤分组
where, group by , order by顺序
7. 子查询:把一条select语句返回的结果用于另一条select语句的where子句
select name from student where student_id in (select student_id from courses where course_name = “人工智能”)
select name, student_id, (select count(*) from course where course.student_id = student.student_id as courses ) from student order by name; // 结果为三列,分别代表学生名,学号,该学生所选的课程数目
8. 联结表
-
内部联结
select * from student inner join courses; (假如两个表各有一个student_id列,就会出现联结后的所有列,可能有重复列) -
自然联结,即结果无重复列,需要自己指定列:
select student_name, course_name from student INNER JOIN courses ON student.student_id = courses.student_id;
相当于
select student_name, course_name from student, courses where student.student_id = courses.student_id;
如果不加where条件即为笛卡尔积,是两个表检索的结果行数m与n的乘积,相当于排列组合。 -
等值联结:select student_name, course_name from student as s, courses as c where s.student_id = c.student_id and s.class = c.class; (可以给表起别名,但是不返回到客户机)
-
自联结: 在一张表中,根据某个字段索引到某一行,再根据该行中的信息进行检索。比如,查询张三所在的班级所有的学生姓名:
可以用子查询:select student_name from student where class = (select class from student where student_name = “张三")
也可以用自联结:select s1.student_name from student as s1, student as s2 where s2.student_name = “张三" and s1.class = s2.class; -
外部联结:以指定的表为基准,显示他的所有行,再与另一个表进行联结。在另一个表中如果没有与指定表相对应的行,就以null填充字段值。左联结就是指定left outer join左边的表,右联结就是指定right outer join右边的表。
-
外部联结与聚集函数、分组结合使用:比如统计每个学生选的课数:
select student_name, count(courses.course) as num_course from student inner join courses on student.student_id = courses.student_id group by student.student_id; (没选课的学生就不会显示)
select student_name, count(courses.course) as num_course from student left outer join courses on student.student_id = courses.student_id group bu student.student_id; (没选课的学生显示的num_course为0)
9. 组合查询
有两种基本情况需要使用组合查询:
- 在单个查询中从不同的表返回类似结构的数据。
- 对单个表执行多个查询,按单个查询返回数据。
select * from table where col1=1 or col2=2;
相当于 select * from table where col1=1 union select * from table where col2=2;
10.全文本搜索
建立表的时候声明(fulltext(col_name))或者建立完以后再设置,本质是设置索引,便可以对该字段进行全文检索。
select col1 from table where Match(col1) Against(‘rabbit’);
结果会以ranking值由高到低进行排序,优先级更高的排前面(’rabbit出现的比较早,多关键词时涵盖的比较多?‘)。
查询扩展:select col1 from table where Match(col1) Against(‘rabbit’ with query expansion)
布尔模式不需要建索引: select col1 from table where Match(col1) Against(‘rabbit’ in boolean mode)
11. 插入数据
insert into student values(“张三”, “10086”); // 简单,但是依赖于列的次序,不安全,不推荐
insert low_priority into student (student_name, student_id) values (“张三”, ”10086“); // 如果某列可以为null,或有默认值,则可以不指定。low_priority指定优先级较低,因为insert比较耗时,如果检索更重要,可以这样设置
插入多个行: insert into student (student_name, student_id) values (“张三”, “10086”), (“李四”, “10087”);
- 插入检索出的数据(insert select)
insert into student (student_name, student_id) select student_name, student_id from courses; // 列名不需要一样
12.更新和删除数据
- 更新
update student set student_name = “李阳” where student_id=“10086”;
update student set student_name=null where student_id=“10086”;
update ignore student set student_name = “liyang”, student_email = “liyang@mail.com” where student_id=“10086”; // ignore忽略更新出错的行,否则一行出错所有的都更新失败
- 删除
delete from student where student_name = “liyang”;
如果要删除表中所有数据, 使用truncate table_name, 实质是删除原有的表并重新创建一个表,效率比逐行删除高很多。
13. 创建和操纵表
创建表:
create table student(
student_id int not null aoto_increment,
student_name char(50) not null default “liyang”,
primary key (student_id)
)engine=InnoDB;
联合主键 primary key(student_id, student_name)
auto_increment原理?如何应对多线程场景?
引擎默认MyISAM,支持全文本搜索,不支持事务处理。
InnoDB是一个可靠的事务处理引擎,不支持全文本搜索;
MEMORY在功能上等同于MyISAM,但是数据存储在内存中,速度很快。
同一个数据库里的表可以混用引擎,但是外键不能跨引擎。
改变表结构:
alter table student add student_email char(50);
alter table student drop column student_emial;
定义外键:alter table student add constraint fk_student_id foreign key (student_id) references courses (student_id);
重命名表:rename table students to student_information;
14.使用视图
视图:以sql查询的结果(不包含数据,只是对sql查询进行封装)作为虚拟表
作用:sql语句重用,简化复杂的sql语句,保护数据(只授予客户特定部分的访问权限而不是整张表)
show create view view_name; 查看创建视图的语句
drop view view_name; 删除视图
drop view if exists view_name;
create or replace view view_name as sql_sentence
重新格式化检索出的数据:
create view view_name as select concat(col1, “(”, col2, “)”) as concat_col from table_name;
select * from view_name;
可以用于更新,但是有限制(只有视图中的每一行都能对应到基表中的一行里的一部分才行),一般来说用于检索。
15.存储过程
存储过程:为了以后的使用而保存的mysql语句的集合。
封装一个完整的操作,易于使用,提高复用性、一致性、可维护性
,提高性能,安全
创建存储过程
create procedure productpricing()
begin
//sql
end;
call productpricing();
mysql命令行临时改变分隔符(begin,end里面sql语句之间会有分号):delimiter //
drop procedure procedure_name; 删除存储过程
create procedure productpricing(
in number int;
out pl decimal(8,2),
out ph decimal(8,2),
out pa decimal(8,2)
)
begin
// sql
selece min(prod_price)
into pl
from product;
end;
call productpricing(2000, @pricelow, @pricehigh, @priveavg);
select @pricelow, @peicehigh, @priceavg; //现实输出的三个变量
show create procedure procedure_name; //现实创建存储过程的语句
16. 游标
- 功能:逐条取检索结果中的数据
CREATE PROCEDURE process()
BEGIN
– declare local variables
DECLARE done INT DEFAULT 0;
DECLARE o INT;
– declare the cursor
DECLARE cursor_name CURSOR FOR
SELECT name from myTable;
– declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;
– open the cursor
OPEN cursor_name;
REPEAT
FETCH cursor_name INTO o;
UNTIL done END REPEAT;
– close the cursor
CLOSE cursor_name;
END;
17.触发器
mysql响应delete、update、insert而自动执行的一条mysql语句。
显示现有的触发器:
SHOW TRIGGERS;
创建触发器:
DROP TRIGGER IF EXISTS learning_db.trigger_insert;
CREATE TRIGGER trigger_insert AFTER INSERT ON myTable
FOR EACH ROW
INSERT INTO learning_db.logs
(info) VALUES(concat("added col1 ",new.col1));
INSERT INTO myTable (col1, col2) VALUES (4, 5);
这里的new是插入的新的值,删除和更新的时候还有一个old变量引用的是旧值。
删除触发器:
DROP TRIGGER IF EXISTS trigger_name;
限制:
- 不能返回select *; 只能保存到变量里,也就是加上 into @param;
- 在触发器所触发的行为里,不能对所操作的表进行更改(增、删、改),只能对其他表进行更改
- 不能有事务的开始和结束(这点只是看到别人这么说,没自己试验过,因为还没看到事务)
- 不有动态sql语句call()
18. 管理事务处理
-
为什么需要事务处理?
事务处理可以用来维护数据库的完整性,他可以保证成批的MySQL操作要么全部执行,要么全都不执行。如果没有错误发生,整组语句提交给数据库。如果发生错误,则进行回退以恢复数据库到某个已知且安全的状态。 -
名词定义
- 事务(transaction):一组SQL语句。
- 回退(rollback): 撤销整组或指定的SQL语句。只能回退增insert、删delete、改update,不能回退create或drop。
- 提交(commit):将未存储的SQL语句结果写入数据库表。
- 保留点(savepoint):事务处理中设置的临时占位符,可以对它发布回退,而不是回退整个事务。
-
事务与回滚的简单示例
START TRANSACTION;
DELETE FROM myTable;
SELECT * FROM myTable; //什么都没有
ROLLBACK; // 回滚上面的操作,结束事务
SELECT * FROM myTable; // 显示删除前所有的数据 -
使用commit
一般来说,MySQL语句是隐含提交的,但是在事务中没有隐含提交,必须显式的手动commit,才能把SQL操作提交到数据库。使用commit或rollback后,事务关闭。
START TRANSACTION;
DELETE FROM myTable;
SELECT * FROM myTable; //什么都没有
COMMIT;
所有操作不出错才会commit,只要出错就不会commit,防止部分更新。 -
使用占位符
START TRANSACTION;
DELETE FROM myTable where id=1;
SELECT * FROM myTable; //什么都没有
SAVEPOINT delete1;
DELETE FROM myTable where id=2;
SELECT * FROM myTable; //什么都没有
SAVEPOINT delete2;
ROLLBACK TO delete1; // 回滚delete2
SELECT * FROM myTable; // 只删除了id=1的数据,id=2的数据还在 -
更改默认提交行为
SET autocommit=0;
19. 全球化和本地化
字符集,编码,校对(规定字符如何比较)
查看数据库所用的字符集和校对方式:
SHOW VARIABLES LIKE “character%”;
SHOW VARIABLES LIKE “collation%”;
创建表的时候设置表或字段的字符集与校对:
create table myTable(
col1 int;
col2 int character set lation2 collate latin1_general_ci;
)default character set hebrew
collate hebrew_general_ci;
order by 或者group by、聚集函数等操作的时候可以设置校对方式,覆盖表的校对方式:
select * from myTable order by name collate latin1_genera_ci;
20. 安全管理
- 访问控制
CREATE USER username IDENTIFIED BY “password”;
RENAME USER username TO newname;
DROP USER newname;
-
查看所有用户:
use mysql;
SELECT user FROM user; -
现实场景中如非必要,决不要使用root账户,因为root账户拥有对数据库的所有权限。
要创建一系列账户,给他们分配不同的权限。 -
显示用户权限: SHOW GRANTS FOR username;
结果显示:GRANT USAGE ON . TOusername
@%
表示什么权限都没有 -
用GRANT赋予权限,REVOKE撤销权限
GRANT SELECT ON dbname.* TO username;
REVOKE SELECT ON dbname.* TO username; -
更改用户密码:
SET PASSWORD FOR username = Password(“newpassword”)
21. 数据库维护
-
备份数据
mysqldump -u root -h localhost -p dbname >backup.sql
mysqldump -u root -h localhost -p dbname table1, table2 >backup.sql
mysqldump -u root -h localhost -p --databases db1, db2 >backup.sql
mysqlhotcopy -u root -h localhost -p dbname > /path/new_directory
SELECT * FROM dbname.Mytable INTO OUTFILE “path/filename” -
数据库维护
ANALYZE TABLE Mytable;
CHECK TABLE table_name;
OPTIMIZE TABLE table_name; //删除大量数据后使用 -
日志
错误日志,查询日志(记录所有活动),二进制日志(记录所有更新),缓慢查询日志(可以用于优化)