MySQL个人学习笔记
目录：
一、数据库的基本操作
-- 选择要操作的数据库 -- world:数据库名 use world; -- 显示已存在的数据库列表 show databases; -- 显示指定数据库下的表的信息 show tables; -- 显示指定表的列的信息 -- world.country:数据库名.表名 show columns from world.country; -- 显示指定表的索引信息 -- world.country:数据库名.表名 show index from world.country; -- 显示指定数据库下的表的详细信息 -- world:数据库名 show table status from world; -- 显示指定数据库下的表名称以字母'c'开头的表的详细信息 -- world:数据库名 show table status from world like 'c%'; -- 显示数据库表的结构，如：字段名，字段类型等 -- world.country:数据库名.表名 describe world.country; -- 查看创建表的SQL语句 -- demo.test：数据库名.表名 show create table demo.test; -- 查看创建存储过程的SQL语句 -- demo.test_proc：数据库名.存储过程名 show create procedure demo.test_proc; -- 查看创建视图的SQL语句 -- demo.test_view：数据库名.视图名 show create view demo.test_view; -- 查看创建函数的SQL语句 -- demo.test_fun：数据库名.函数名 show create function demo.test_fun; -- 查看当前用户的数据库权限 show grants; -- 查看指定用户的数据库权限 -- admin@localhost:用户名@访问主机 show grants for 'admin'@'localhost'; -- 查询数据库用户信息 select * from mysql.user; -- 获取服务器版本信息 SELECT VERSION(); -- 获取当前数据库名 (或者返回空) SELECT DATABASE(); -- 获取当前用户名 SELECT USER(); -- 获取服务器状态 SHOW STATUS; -- 获取服务器配置变量 SHOW VARIABLES; 例如： -- 查询自增长值的步长，即每次增加多少，默认为1。 show variables like '%auto_increment%'; -- 设置自增长值每次增加的数值，会影响所有数据表。 set auto_increment_increment=3; -- 设置自增长值的起始值，会影响所有数据表。 set auto_increment_offset=100; -- mysql运行在安全模式下时，非主键条件下是无法执行update或者delete命令的 -- 查看安全模式状态 show variables like '%sql_safe_updates%'; -- 设置安全模式为关闭 set sql_safe_updates=off; -- 获取最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值，@@identity是系统定义的全局变量。 select @@identity; -- LAST_INSERT_ID函数将返回当前连接自增列最新的 insert or update 操作生成的第一个记录的ID。因为其基于Connection的，所以也是线程安全的。 select LAST_INSERT_ID();
二、创建、删除用户及授权
-- 创建一个新的用户，并设置登录密码
-- test:用户名;localhost:本地主机访问(如果需要其他任意主机访问，请使用通配符'%');123456:用户密码;
create user 'test'@'localhost' identified by '123456';
-- 创建一个新的用户，不指定登录密码，即不需要登录密码
create user 'test01'@'localhost';
-- 删除指定的用户
drop user 'test01'@'localhost';
-- 修改用户名
-- test@localhost:要修改的用户名和访问主机
-- test@%:修改为的用户名和访问主机
rename user 'test'@'localhost' to 'test'@'%';
-- 修改用户密码
-- test@localhost:要修改的用户名和访问主机
-- 123456:新的用户密码
set password for 'test'@'localhost' = Password('123456');-- 授予指定用户'test'对于'world'数据库下'country'表的查询权限
-- select:查询权限;world.country:数据库名.表名;'test'@'localhost':用户名@访问主机;
grant select on world.country to 'test'@'localhost';
-- 立即启用修改(默认再次登录才会生效)
flush privileges;
-- 撤销指定用户'test'对于'world'数据库下'country'表的查询权限
-- select:查询权限;world.country:数据库名.表名;'test'@'localhost':用户名@访问主机;
revoke select on world.country from 'test'@'localhost';
-- 立即启用修改(默认再次登录才会生效)
flush privileges;
-- 授予指定用户'test'对于'world'数据库下所有表的查询、新增、修改、删除权限
grant select,insert,update,delete on world.* to 'test'@'localhost';
-- 撤销指定用户'test'对于'world'数据库下所有表的查询、新增、修改、删除权限
revoke select,insert,update,delete on world.* from 'test'@'localhost';
-- 授予指定用户'test'对于'world'数据库下所有表的表结构进行创建、修改、删除权限
grant create,alter,drop on world.* to 'test'@'localhost';
-- 撤销指定用户'test'对于'world'数据库下所有表的表结构进行创建、修改、删除权限
revoke create,alter,drop on world.* from 'test'@'localhost';
-- 授予指定用户'test'对于'world'数据库下所有存储过程的执行权限，并且该用户有权限转授予其他用户
grant execute on world.* to 'test'@'localhost' with grant option;
-- 撤销指定用户'test'对于'world'数据库下所有存储过程的执行权限，转授予权限一并撤销
revoke execute on world.* from 'test'@'localhost';
三、数据库字符校对集
字符校对集，即排序规则，在某个字符集的情况下，字符集的排列顺序应该是什么，称之为校对集。
-- 查看所有的字符校对集 -- 后缀为_bin:表示基于二进制编码的直接比较 -- 后缀为_ci:表示对大小写不敏感的比较 -- 后缀为_cs:表示对大小写敏感的比较 show collation;
四、创建、删除数据库和表
-- 创建一个名为'test'的数据库
create database test;
-- 创建一个名为'test'的数据库，如果该数据库已存在则不创建，否则再创建
-- 并指定默认字符集为'utf8'，字符校对集为'utf8_general_ci'
create database if not exists test default charset utf8 collate utf8_general_ci;
-- 删除名为'test'的数据库
drop database test;
-- 创建一个名为'Student'的数据表，如果该数据表已存在则不创建，否则再创建
-- engine:指定数据库引擎为'InnoDB'
-- auto_increment:指定自增列的起始值为1
create table if not exists Student
(
ID int not null auto_increment, #自动增长列
StuNo varchar(32) not null,
StuName varchar(8) not null,
StuSex varchar(8) null,
StuBirthday tinyint null,
CreateTime datetime null,
primary key (ID) #指定主键列
)
engine=InnoDB auto_increment=1 default charset=utf8 collate=utf8_general_ci;
-- 删除数据表 student，该操作会删除所有数据包括表结构、视图、索引、约束等。
drop table test.student;
-- 删除数据表中的所有数据，该操作会删除表中所有的数据，但是会保留表结构、视图、索引、约束等。
truncate table test.student;
-- 创建一个临时表，临时表的创建与数据表的创建类似，只不过需要添加关键字 temporary。
-- 临时表的作用域为当前会话，即当前连接可见，当断开当前连接时会自动销毁，当然也可以手动删除，删除方式与数据表一样。
create temporary table Product
(
ProName varchar(32) not null,
Price decimal(10,3) not null default 0.000
);
-- 复制指定数据表的表结构到创建的新表。
create table test.StudentBak like test.student;
-- 复制指定数据表的表结构及所有数据到创建的新表。
create table test.StudentBak select * from test.student;
五、DML操作
-- 向数据表中插入数据
insert into student(StuNo,StuName,StuSex,Stubirthday,CreateTime)
select 'A001','小张','男',str_to_date('1988-06-09','%Y-%m-%d'),current_timestamp() union all
select 'A002','小红','女',str_to_date('1990-08-10','%Y-%m-%d'),current_timestamp()
-- 在插入重复的数据时，会直接跳过重复数据的插入。在有自增列或主键的数据表中不起作用，因为自增列和主键都具有唯一性。
insert ignore into test.student(stuno,stuname,stusex,stubirthday,createtime)
values ('A003','小鱼','女','1991-07-07',current_timestamp());
-- MySQL的WHERE子句默认是不区分大小写的，如果需要区分大小写，就要在字段前加上关键字 binary
select * from student where stuno='a001'; #'1', 'A001', '小张', '男', '1988-06-09', '2018-01-12 12:17:00'
select * from student where binary stuno='a001'; #null
-- limit:用于设置返回的记录数。
-- offset:用于设置select语句开始查询的数据偏移量，默认为零。
-- 表示只取前10条数据
select * from world.city limit 10;
-- 表示跃过5条，从第6条数据开始取10条数据。
select * from world.city limit 10 offset 5;
-- 表示从第10条开始取5条数据。
select * from world.city limit 10,5;
-- regexp:用于设置正则表达式匹配项，类似于模糊匹配like。
-- 表示查询名称以字符 'A'(不区分大小写)开头的记录。
select * from world.city where Name regexp '^A';
-- 表示查询名称中包含字符串 'mer' 的记录。
select * from world.city where Name regexp 'mer';
-- 表示查询名称以字符 'a' 或字符 'b' 开头的记录或者以字符 'r' 结尾的记录。
select * from world.city where Name regexp '^[ab]|r$';
六、DDL操作
-- 向指定数据表添加一列，默认添加到数据表字段的末尾。 alter table test.student add column1 varchar(10) null; -- 向指定数据表添加一列，并设置默认值为：0 alter table demo.chinesecharinfo add column IsUseful tinyint unsigned not null default 0; -- first关键字用于把添加的列设置为第一列。 alter table test.student add column1 varchar(10) null first; -- after关键字用于把添加的列设置在指定列的后面，StuSex为指定列的列名。 alter table test.student add column1 varchar(10) null after StuSex; -- 删除指定列名的列，当数据表仅剩一个字段时，无法进行删除。 alter table test.student drop column1; -- 修改指定列的数据类型，并设置该列位于指定列名的列之后。 alter table test.student modify column1 char(10) null after CreateTime; -- 关键字column可省略 alter table test.student modify column column1 char(10) null after CreateTime; -- 修改指定列的列名和数据类型，并设置该列位于指定列名的列之后。 -- column1:为原列名 -- column2:为新的列名 alter table test.student change column1 column2 varchar(10) null after CreateTime; -- 修改指定列的默认值。 alter table test.student alter column2 set default '123'; -- 删除指定列的默认值。 alter table test.student alter column2 drop default; -- 修改数据表的存储引擎。 alter table test.student engine = myisam; alter table test.student engine = InnoDB; -- 修改数据表的自增长值的起始值。 alter table test.student auto_increment=10; -- 重建自增长列，当删除数据过多，自增长列的值比较混乱时可以使用，但是重建时如果有新的数据插入，有可能会出现混乱。 alter table test.student drop ID; alter table test.student add ID int not null auto_increment first; alter table test.student add primary key(ID); -- 修改数据表的表名称。 alter table test.student rename to test.StudentBak;
七、索引
-- 查看指定数据表的索引。 show index from test.student; -- 删除指定的索引。 drop index index_name on test.student; -- 修改表结构的方式删除索引。 alter table test.student drop index index_name; -- 创建普通索引。 create index index_name on test.student(StuNo); -- 修改表结构的方式添加索引，这种方式可以不指定索引名称，不指定系统会自动默认一个索引名称。 alter table test.student add index index_name(StuNo); -- 创建唯一索引，指定创建唯一索引的列的值必须是唯一的，不能重复，但是可以为null。 create unique index index_name on test.student(StuNo); -- 修改表结构的方式添加唯一索引。 alter table test.student add unique index index_name(StuNo); -- 修改表结构的方式添加主键，必须保证添加主键的列的值不能为null，并且是唯一的，不可重复。 alter table test.student add primary key PrimaryKey_Name(ID); -- 删除指定数据表的主键，删除主键时只需指定 primary key，删除索引时必须指定索引名。 -- 注意：当主键列同时是自增长列时，不能直接删除主键，需要先删除自增长约束。 alter table test.student drop primary key; -- 添加全文索引。 alter table test.student add fulltext index_name(StuNo); -- 加上关键字ignore创建的唯一索引和主键，在插入重复数据时，会直接过滤掉重复数据，并且不会报错，否则就会抛出错误。 alter ignore table test.student add primary key(ID); alter ignore table test.student add unique index index_name(StuNo);
八、事务
-- 关闭自动提交事务
set autocommit=0;
-- 开启自动提交事务，默认为开启。
set autocommit=1;
-- 显式地开启一个事务，有以下两种方法。
start transaction;
begin;
-- commit用于提交事务，只有当自动提交事务被关闭时需要使用。
commit;
-- rollback用于回滚事务，撤销对于数据库所做的未提交的操作。
rollback;
-- 用于设置一个保存点，identifier是指保存点的名称。
savepoint identifier;
-- 用于删除一个保存点，如果指定的保存点不存在，将会抛出一个异常。
release savepoint identifier;
-- 把事务回滚到指定的保存点。
rollback to identifier;
-- 设置事务隔离级别，只对下一个事务有效。
set transaction isolation level {事务隔离级别};
-- 设置事务隔离级别，对当前会话的事务有效。
set session transaction isolation level {事务隔离级别};
-- 设置事务隔离级别，对后面建立MySQL连接的事务有效。
set global transaction isolation level {事务隔离级别};
-- 事务的隔离级别
read uncommitted(读取未提交):
-- 该级别引发的问题是脏读，会读取到其他事务未提交的数据。
read committed(读取已提交):
-- 该级别引发的问题是不可重复读，即设置为该级别的事务只能读取到其他事务已经提交的数据，未提交的数据不能读取，会造成多次查询的结果不一致。
repeatable read(可重复读):
-- 该级别引发的问题是幻读，即当用户修改某一范围内的数据行时，另一个事务又在该范围内插入了新的行，当用户再次读取该范围内的数据时，会发现有新的数据行没有被修改。
-- 该级别是MySQL数据库默认的事务隔离级别。注意：该级别不会对事务查询到的行加行锁，也就是该事务查询到的行，其他事务依然能进行修改，但是能保证数据的一致性。
serializable(可串行化):
-- 该级别是MySQL中事务隔离级别最高的，该级别会锁住事务中操作的整张表，因此不会出现以上三个级别的问题。但是这种隔离级别并发性极地，开发中很少会用到。