My SQL个人学习笔记

2018-02-07 20:13:10来源:cnblogs.com作者:Brambling人点击

分享

目录:

  数据库的基本操作

  创建、删除用户及授权

  数据库字符校对集

  创建、删除数据库和表

  DML操作

  DDL操作

  索引

  事务

一、数据库的基本操作

-- 选择要操作的数据库-- 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;        -- 查看当前用户的数据库权限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;-- 获取最近一次向具有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' = '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:指定自增列的起始值为1create 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子句默认是不区分大小写的,如果需要区分大小写,就要在字段前加上关键字 binaryselect * 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;-- 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;-- 修改指定列的列名和数据类型,并设置该列位于指定列名的列之后。-- 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中事务隔离级别最高的,该级别会锁住事务中操作的整张表,因此不会出现以上三个级别的问题。但是这种隔离级别并发性极地,开发中很少会用到。

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台