MySQL

MySQL

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification，create_specification…]

create_specification：

CHARACTER SET：指定数据库采用的字符集，如果不指定字符集，默认utf8

COLLATE：指定数据库字符集的校对规则(常用的utf8_bin，utf8_general_ci 注意默认是utf8_general_ci)

IF NOT EXISTS：如果不存在

create database[if not exists] mydb01 character set utf8 collate utf8_general_ci;

查询、删除数据库

显示数据库语句：

SHOW DATABASES

显示数据库创建语句：

SHOW CREATE DATABASE db_name

数据库删除语句：

DROP DATABASE [IF EXISTS] db_name

IF EXISTS：如果存在

show databases;-- (显示数据库)
drop database[if exists] mydb01; -- (如果存在，就删除mydb01数据库)

备份、恢复数据库

• 备份数据库(注意：在DOS下执行)

  mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 目录\\文件名.sql

• 恢复数据库(注意：在DOS下SQL登录后执行)

  Source 目录\\文件名.sql

  可以将数据导入到别的数据库里

  mysqldump -u root -p -B mydb01 > F:\\mydb01.sql -- (把mydb01数据库被分到f盘的mydb01.sql文件中)
  Source F:\\mydb01.sql -- (从f盘的mydb01.sql文件中恢复数据库)
  

创建表

CREATE TABLE table_name

(

​ field1 datatype,

​ field2 datatype,

​ field3 datatype

)character set 字符集 collate 校对规则 engine引擎

field：指定列名

datatype：指定列类型

character set：如果不指定字符集，则为所在数据库字符集

collate：如不指定则为所在数据库校对规则

engine：引擎(涉及内容较多，这里不细写)

create table stu
(
    id int not null default 1,
    name varchar(20) not null default '',
    chinese float not null default 0.0,
    english float not null default 0.0,
    math float not null default 0.0
)character set utf8 collate utf8_bin engine innodb;

MySQL常用数据类型

• 数值型(整数)的基本使用

• 说明：在能够满足需求的情况下，尽量选择占用空间小的类型，可以使用UNSIGNED关键字来觉得是否带符号

类型	字节	最小值	最大值
		(带符号的，无符号的)	(带符号的，无符号的)
TINTINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

• 数值型(bit)的使用

  1. 基本使用

     mysql> create table t02 (num bit(8));

     mysql> insert into t02(1,3);

     mysql> insert into t02 values(2,65);

  2. 细节说明

  • bit字段显示时，按照位的方式显示
  • 查询的时候仍然可以使用，添加的数值
  • 如果一个值只有0,1可以考虑使用bit(1)，可以节约空间
  • 位类型，M指定位数，默认值1，范围1-64
  • 使用不多

• 数值型(小数)的基本使用

1. FlOAT/DOUBLE [UNSIGNED]

   Float单精度，Double双精度

2. DECIMAL[M, D] [UNSIGNED]

• 可以支持更加精确的小数位，M是小数位数(精度)的总数，D是小数点(标度)后面的位数

• 如果D是0，则值没有小数点或分数部分，M最大65，D最大是30，如果D被省略，默认是0，如果M被省略，默认是10

• 建议：如果希望小数的精度高，推荐使用DECIMAL

• 字符串的基本使用

1. CHAR(size)

   固定长度字符串，最大255字符，定多少空间就分配多少，不管资源是否浪费

2. VARCHAR(size)

   可变长度字符串，最大65535字节 [utf8编码最大21844字符 1-3个字节用于记录大小]，如果你插入了2个字符而实际规定的是4个字符，那么会按照2个字符来分配空间

3. size表示字符数，不管是中文还是字母都是最多放规定的个数，是按照字符存放的

4. 如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等

5. 如果一个字段的长度是不确定的，那就使用varchar，比如留言，文章

6. 查询速度：char > varchar

7. 在存放文本时，也可以使用Text数据类型，可以将Text列视为varchar列，注意Text列不能有默认值，大小0-2^16字节，如果希望存放更多字符，可以选择MEDIUMTEXT 0-2^24 或者 LONGTEXT 0~2^32

• 日期类型的基本使用

  CREATE TABLE birthday6

  (t1 DATE, t2 DATETIME,

  ​ t3 TIMESTAMP NOT NULL DEFAULT

  CURRENT_TIMESTAMP ONUPDARE

  CURRENT_TIMESTAMP

  ); timestamp时间戳

• 修改表

  使用ALTER TABLE 语句追加，修改，或删除列的语法

  添加列： ALTER TABLE tablename

  ​ ADD (column datatype [DEFAULT expr], [column datatype]…);

  ​ ALTER TABLE emp ADD image VARCHAR(32) NOT NULL DEFAULT ‘’ AFTER RESUME ;

  修改列： ALTER TABLE tablename

  ​ MODIFY (column datatype [DEFAULT expr], [column datatype]…);

    	   ALTER TABLE emp MODIFY job VARCHAR(60) ;
  

  删除列： ALTER TABLE tablename

  ​ DROP (column);

  ​ ALTER TABLE emp DROP image;

  修改列名： ALTER TABLE tablename

  ​ CHANGE 列名 新列名 类型

  ​ ALTER TABLE emp CHANGE name user_name VARCHAR(64) NOT NULL DEFAULT’’;

  查看表结构：desc 表名;

  修改表名：Rename table 表名 to 新表名

  修改字符集：alter table 表名 character set 字符集;

• 数据库C[create]R[read]U[update]D[delete]语句

1. Insert语句 (添加数据)
2. Update语句 (更新数据)
3. Delete语句 (删除数据)
4. Select语句 (查找数据)

• Insert语句

  INSERT INTO table_name [(column [, column…])] VALUES (value [, value…])

• 使用注意细节：

1. 插入数据英语字段的数据类型相同，比如把’abc’添加到int 类型会错误

2. 数据的长度应在猎德规定范围内，例如：不能讲一个长度为80的字符串加入到长度为40的列中

3. 在values中列出的数据位置必须与被加入的列的排列位置相对应

4. 字符和日期型数据应包含在单引号内

5. 列可以插入空值[前提是该字段允许置空]，insert into table value (null)

6. inset into tab_name (列名…) values (),(),()形式添加多条记录

7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称

8. 默认值的使用，当不给某个字段值时，如果有 默认值就会添加，否则会报错

   insert into stu values (1,'老刘',89,78,90) (2,'张飞',5,2,3) (3,'关羽',89,98,88);-- (在表中添加三条数据)
   

• update修改表中数据

  UPDATE tab_name SET col_name=expr [, col_name2=expr2…] [WHERE where_definition]

  update stu set math=60 where name ='小明';-- (将名字为小明的记录中的数学成绩改为60分)
  

• delete删除表中数据

  delete from stu where name='张三';-- (将名字为张三的数据从表中删除)
  delete from stu;-- (删除stu表中的所有数据)
  drop table stu;-- (删除stu表)
  

• select

SELECT [DISTINCT] * |{column1, column2, column3…} FROM table_name;

• 注意事项

1. Select指定查询哪些列的数据

2. column指定列名

3. *表示查询所有列

4. From指定查询哪张表

5. DISTINCT可选，指显示结果时，是否去掉重复数据

6. 使用表达式对查询列表进行运算

   SELECT *|{column1|expression(表达式，算式)，column2|exprssion…} FROM table_name;

7. 在select语句中可使用as语句

   SELECT column_name as 别名 FROM 表名

   select * from stu;-- (查询stu表内所有数据)
   select `name`,(chinese+math+english) as 总成绩 from stu;-- (查询总成绩，并且用总成绩来命名相加数据)
   

8. where子句：

   1. 	> < <= >= = <> !=	大于、小于、大于等于、小于等于、不等于
      	BETWEEN…AND…	显示某一区间的值
      比较运算符	IN(set)	显示在in列表中的值，例：in(100,200)
      	LIKE ‘张pattern’、NOT LIKE ‘’	模糊查询、模糊查询
      	IS NULL	判断是否为空
      	and	多个条件同时成立
      逻辑运算符	or	多个条件任一成立
      	not	不成立，例：where not(salary > 100)；

      select * from stu where math>60 and id>2;-- (查询数学成绩大于60并且id大于2的同学)
      select * from stu where name like '关%'; -- (查询姓关的同学)
      select * from stu where english between 80 and 90;-- (查询成绩在80到90之间的同学)
      select * from stu where math in (89,90,91);-- (查询数学成绩为89,90,91的同学)
      select `name`,(chinese+math+english) as 总成绩 from stu where 总成绩 in (189,190,191);-- (查询总成绩为189,190,191的同学)
      select * from stu where math-chinese>30;-- (查询数学比语文多30分的同学)
      

9. order by：

   1. SELECT column1，column2，column3，FROM table; order by column asc|desc

   2. order by 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名

   3. asc 升序，desc 降序 默认是升序

   4. order by 子句应位于select语句的结尾。

      select * from stu order by id desc;-- (以降序排列id，查询stu表中数据)
      

10. 合计/统计函数 -

    1. count

       select count(*)|count(列名) from table_name [WHERE where_definition];

       count 返回行的总数

       select count(*) | count (列名) from table_name [WHERE where_definition] ;

       count(列)：统计满足条件的某列有多少个，但是会排除为null的情况

       count(*)：返回满足条件的记录的行数

    2. sum

       select sum(列名) {，sum(列名)…} from table_name [WHERE where_definition];

       sum 返回表内某一列的和

       只对数值起作用

    3. MAX/MIN

       max/min函数返回满足where条件的一列的最大/最小值

       select max(列名) from table_name [WHERE where_definition];

    4. 分组统计

       1. group by

          select column1，column2，column3… from table_name group by column

       2. having

          select column1，column2，column3… from table_name group by column having…

       3. group by用于查询的结果分组统计，(示意图)

       4. having子句用于限制分组显示结果，和where用法类似

    select count(*) from stu;-- (查询表中一共有多少条数据)
    select sum(math) from stu;-- (返回stu表中math列的和)
    select max(chinese) from stu;-- (查询stu表中语文成绩最高的人)
    select * from stu group by name;-- (用名字进行分组，名字一样的一组)
    select * from stu group by name having math > 60;-- (用名字进行分组，数学成绩大于60分，名字一样的一组)
    

11. 字符串相关函数

    相关函数	作用
    charset(str)	返回字串字符集
    concat(string2 [,…])	连接字串
    inset(string，substring)	返回substring在string中出现的位置，没有返回0
    ucase(string2)	转换成大写
    lcase(string2)	转换成小写
    left/right(string2，length)	从string2中的左边/右边起取length个字符
    length(string)	string长度[按照字节]
    replace (str，search_str，replace_str)	在str中用replace_str替换search_str
    strcmp(string1，string2)	逐字符比较两字串大小
    substring(str, position [, length])	从str的position开始[从1开始计算]，取length个字符
    ltrim(string2)/rtrim(string2)/ trim	去除前端空格或后端空格或前后端空格

    select charset(name) from stu;-- (返回stu表中name列用的字符集)
    select concat(name, '语文成绩:', chinese) from stu;-- (用括号里方式的拼接数据返回)
    select inset('aawerfgf','wer') from dual;-- (返回wer在aawerfgf中出现的位置，从1开始查，dual亚元表，系统表可用于测试)
    select ucase(name) from stu;-- (把name列的小写字母全都转为大写)
    select left(name,2) from stu;-- (从name左边开始取两个字符)
    select length(name) from stu;-- (返回name列每行字符长度)
    select replace('qqddf','df','as') from dual;-- (从qqddf中用as替代df)
    select substring(name,1,2) from stu;-- (从第一个位置开始取两个字符)
    

12. 数学相关函数

    相关函数	作用
    ABS(num)	绝对值
    CEILING(number2)	向上取整，得到比num2大的最小整数
    CONV(number2，from_base，to_base)	进制转换
    FLOOR(number2)	向下取整，得到比num2小的最大整数
    FORMAT(number,decimal_places)	保留小数位数
    HEX(DecimaNumber)	转十六进制
    LEAST(number，number2 [,…])	求最小值
    MOD(numbertor,denominator )	求余
    BIN(decimal_number)	十进制转二进制
    RAND([seed])	RAND([seed])

13. 时间日期相关函数

| 相关函数                                    | 作用                                          |
| ------------------------------------------- | --------------------------------------------- |
| CURRENT_DATE()                              | 当前日期                                      |
| CURRENT_TIME()                              | 当前时间                                      |
| CURRENT_TIMESTMAP()                         | 当前时间戳                                    |
| DATE(datetime)                              | 返回datetime的日期部分                        |
| DATE_ADD(date2, INTERVAL d_value d_type)    | 在date2中加上日期或时间                       |
| DATE_SUB(date2, INTERVAL d_value d_type)    | 在date2上减去一个时间                         |
| DATEDIFF(date1, date2)                      | 两个日期差(结果是天)                          |
| TIMEDIFF(date1, date2)                      | 两个时间差(多少小时多少分钟多少秒)            |
| NOW()                                       | 当前时间                                      |
| YEAR\|Month\|DATE(datetime) FROM_UNIXTIME() | 年月日                                        |
| UNIX_TIMESTAMP（）                          | 返回一个1970-01-01 00:00:00 G之后的秒数的整数 |

14. 加密和系统函数

    相关函数	作用
    USER()	查询用户
    DATABASE()	数据库名称
    MD5(str)	为字符串算出一个MD5 32的字符串，(用户密码)加密
    PASSWORD(str) select * from mysql.user \G	从原文密码str计算并返回密码字符串，通常用于对mysql数据库的用户密码加密

    基本使用

    mysql > create table users(id int,name varchar(32) not null default ‘’, pwd char(32) not null default ‘’);

    添加一个用户名

15. 流程控制函数

    相关函数	作用
    IF(expr1，expr2，expr3)	如果expr1为True，则返回expr2，否则返回expr3
    IFNULL(expr1, expr2)	如果expr1不为NULL，则返回expr1，否则返回expr2
    SELEECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END；[类似多重分支.]	如果expr1为TRUE，则返回expr2，如果expr2为t，返回expr4，否则返回expr5

    select case when true then 'jack' when false then 'tom' else 'mary' end;-- 结果返回jack
    select name, if(math>60,'合格','不合格') from stu;-- 如果math>60返回合格，否则返回不合格
    

16. 分页查询

    语法：

    ​ select… limit start，rows

    ​ 表示从start+1行开始取，取出rows行，start 从0开始计算

    ​ page.sql

    ​ start=每页显示记录数*(第几页-1)，rows=每页显示记录数

    -- 第一页 start=3*(1-1)=0 rows=3 从第0条记录开始取出三行记录 
    select * from stu order by name limit 0,3;
    -- 第二页 start=3*(2-1)=3 rows=3 从第3条记录开始取出三行记录
    select * from stu order by name limit 3,3;
    

17. 如果select语句同时包含有group by ，having, limit, order by, 那么它们的顺序是 group by ，having, order by, limit

18. 如果要查询多个重复的表，那么需要联系两表关键字

19. 约束

    1. 主键：primary key

    2. 唯一的：unique

    3. 外键：foreign key，用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表主键列存在或是为null

       foreign key (本表字段名) references 主表名(主键名或unique字段名)

• mysql索引

  在查询数据时，如果没有索引，那么会进行全表扫描，所以查询速度会很慢

  如果有索引，那么会形成一个索引的数据结构，比如二叉树

  形成索引后，表的磁盘占用会增加，还会对update，delete，insert语句产生印象，因为要维护索引的数据结构

  索引的类型：

  1. 主键索引，主键自动的为主索引(类型primary key)

  2. 唯一索引(unique)

  3. 普通索引(index)

  4. 全文索引(fulltext)[适用于MylSAM]

     一般开发中不适用mysql自带的全文索引，而是使用：全文搜索Solr和ElasticSearch(ES)

  索引的使用

  ​ 查询表是否有索引

  ​ show indexes from t25

  1. 添加唯一索引

     create [unique] index index_name on tbl_name (col_name[(length)] [asc|desc], …);

     alter table table_name ADD INDEX [index_name] (index_col_name, …)

     create unique index id_index on t25 (id);-- (在t25表的id列上创建索引，索引名为id_index，并且是unique，唯一索引)
     create index id_index on t25 (id);-- (在t25表id列上创建普通索引)
     alter table t25 add index id_index (id);-- (创建普通索引方式2)
     alter table t26 add primary key (id);-- (创建主键索引也可以直接在创建表示指定主键，即为主键索引)
     

  2. 添加主键索引

     alter table 表名 add primary key (列名, …);

  3. 删除索引

     DROP index 索引名 on 表名

  4. 删除主键索引

     alter table 表名 drop primary key

  5. 修改索引就是先删除，在添加新的索引

     小结：那些列上适合使用索引

     1. 较频繁的作为查询条件字段应该创建索引

        select * from emp where empno = 1

     2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

        select * from emp where sex=‘男’

     3. 更新非常频繁的字段不适合创建索引

        select * from emp where loginconut = 1

     4. 不会出现在where子句中字段不该创建索引

• 事务

  事务用于保证数据的一致性，它有一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败，如：转账就要用事务处理，用以保证数据一致性

  当执行事务操作(dml语句)时，mysql会在表上加锁，防止其他用户改表的数据，这对用户来讲是非常重要的

  mysql数据库控制台事务的几个重要操作

  1. start transaction – 开始一个事务
  2. savepoint 保存点名 --设置一个保存点
  3. rollback to 保存点名 --回退事务
  4. rollback --回退全部事务
  5. commit --提交事务，所有操作生效，不能回退

  注意事项：

  1. 如果不开启事务，默认情况下，dml操作时自动提交的，不能回滚
  2. 如果开始一个事务，你没有创建保存点，你可以执行rollback，默认就是回退到你事务开始的状态
  3. 你也可以在这个事务中(还没有提交时)，创建多个保存点，比如：savepoint aa；执行dml，savepoint bbb；
  4. 你可以在事务没有提交前，选择回退到那个保存点
  5. mysql的事务机制需要innodb的储存引擎才可以使用，myisam不好使
  6. 开始一个事务 start transaction，set autocommit=off；

  事务的隔离级别

  1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。

  2. 如果不考虑隔离性，可能会引发如下问题：

     脏读(dirty read)：当一个事务读取另一个事务尚未提交的修改时，产生脏读

     不可重复读(nonrepeatable read)：同一查询在同一事物中多次进行，由于其他连接提交事务所做的修改或删除，每次都返回不同结果集，此时发生不可重复读

     幻读(phantom read)：同一查询在同一事务中多次进行，由于其他连接提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读

     MySQL隔离级别	脏读	不可重复读	幻读	加锁读
     读未提交(Read uncommitted)	√	√	√	不加锁
     读已提交(Read committed)	×	√	√	不加锁
     可重复读(Repeatable read)	×	×	×	不加锁
     可串行化(serializable)	×	×	×	加锁

     说明：√可能出现 ×不会出现

  3. 查看当前会话隔离级别

     select @@tx_isolation;

  4. 查看系统当前隔离级别

     select @@global.tx_isolation;

  5. 设置当前会话隔离级别

     set session transaction isolation level 隔离级别;

  6. 设置当前系统隔离级别

     set global transaction Isolation level 隔离级别;

  7. mysql 默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改(因为该级别已经满足绝大部分项目需求)

• mysql表类型和储存引擎

  特点	Myisam	Innodb		Memory	Archive
  批量插入的速度	高	低		高	非常高
  事务安全		支持
  全文索引	支持
  锁机制	表锁	行锁		表锁	行锁
  存储限制	没有	64TB		有	没有
  B树索引	支持	支持		支持
  哈希索引		支持		支持
  集群索引		支持
  数据缓存		支持		支持
  索引缓存	支持	支持		支持
  数据可压缩	支持				支持
  空间使用	低	高		N/A	非常低
  内存使用	低	高		中等	低
  支持外键		支持

  1. Myisam不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
  2. Innodb存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，但是比起Myisam存储引擎，Innodb写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引
  3. Memory存储引擎使用存在内存中的内容来创建表。每个Memory表只实际对应一个磁盘文件。Memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用Hash索引，但是一旦服务关闭表中的数据就会丢失掉，表的结构还在
  4. alter table 表名 engine = 储存引擎;

• 视图

  1. 视图是根据基表(可以是多个基表)来创建的 视图是虚拟的表

  2. 视图也有列，数据来自基表

  3. 通过视图可以修改基表的数据

  4. 基表的改变也会影响到视图的数据

     视图的基本使用

  5. create view 视图名 as select语句

  6. alter view 视图名 as select语句

  7. show create view 视图名

  8. DROP view 视图名1，视图名2

     create view emp_view01 as select empno,ename,job, deptno from ep;-- 创建视图
     desc emp_view01;-- 查看视图
     show create view emp_view01;-- 查看创建视图的指令
     drop view emp_view01;-- 删除视图
     

  9. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式：视图名.frm)

  10. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert，update，delete]

• 用户管理

  1. 创建用户

     create user ‘用户名’ @ ‘允许登录位置’ identified by ‘密码’

     创建用户同时指定密码

  2. drop user ‘用户名’ @ ‘允许登录位置’;

  3. 修改密码

     修改自己的密码

     set password = password(‘密码’);

     修改他人的密码(需要有修改用户密码的权限)

     set password for ‘用户名’@‘登录位置’ = password(‘密码’);

• 权限管理

1. 给用户授权

   语法：grant 权限列表 on 库.表名 to ‘用户名’ @ ‘登录位置’ [identified by]

2. 回收用户权限

   语法：revoke 权限列表 on 库.表名 from ‘用户名’ @ ‘登录位置’;

3. 权限生效指令

   如果权限没有生效，可以执行下面命令

   flush privileges;

   grant select,insert on 数据库.表名 to 'mk' @ 'localhost';
   

据库去看，对应视图只有一个视图结构文件(形式：视图名.frm)

6. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert，update，delete]

推荐b站韩顺平老师的课