Mysql基本语法（Mysql必知必会阅读笔记）

这两天系统的复习了一下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；

限制：

1. 不能返回select *； 只能保存到变量里，也就是加上 into @param；
2. 在触发器所触发的行为里，不能对所操作的表进行更改（增、删、改），只能对其他表进行更改
3. 不能有事务的开始和结束（这点只是看到别人这么说，没自己试验过，因为还没看到事务）
4. 不有动态sql语句call()

18. 管理事务处理

• 为什么需要事务处理？
  事务处理可以用来维护数据库的完整性，他可以保证成批的MySQL操作要么全部执行，要么全都不执行。如果没有错误发生，整组语句提交给数据库。如果发生错误，则进行回退以恢复数据库到某个已知且安全的状态。

• 名词定义

1. 事务（transaction）：一组SQL语句。
2. 回退（rollback）： 撤销整组或指定的SQL语句。只能回退增insert、删delete、改update，不能回退create或drop。
3. 提交（commit）：将未存储的SQL语句结果写入数据库表。
4. 保留点（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;

1. 查看所有用户：
   use mysql;
   SELECT user FROM user;

2. 现实场景中如非必要，决不要使用root账户，因为root账户拥有对数据库的所有权限。
   要创建一系列账户，给他们分配不同的权限。

3. 显示用户权限： SHOW GRANTS FOR username;
   结果显示：GRANT USAGE ON . TO username@% 表示什么权限都没有

4. 用GRANT赋予权限,REVOKE撤销权限
   GRANT SELECT ON dbname.* TO username;
   REVOKE SELECT ON dbname.* TO username;

5. 更改用户密码：
   SET PASSWORD FOR username = Password(“newpassword”)

21. 数据库维护

1. 备份数据
   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”

2. 数据库维护
   ANALYZE TABLE Mytable；
   CHECK TABLE table_name;
   OPTIMIZE TABLE table_name; //删除大量数据后使用

3. 日志
   错误日志，查询日志（记录所有活动），二进制日志（记录所有更新），缓慢查询日志（可以用于优化）