Mysql数据库详解

1、mysql是中型关系型数据库操作。

 

SQL 主要分为三种：

• DDL：Data Definition Language，数据定义语言，用来维护存储数据的结构（数据库、表），代表指令为create、drop和alter等。
• DML：Data Manipulation Language，数据操作语言，用来对数据进行操作（表中的内容）代表指令为insert、delete和update等，不过在 DML 内部又单独进行了一个分类，即 DQL（Data Query Language），数据查询语言，代表指令为select.
• DCL：Data Control Language，数据控制语言，主要是负责（用户）权限管理，代表指令为grant和revoke等。

（1）新增数据库

create database dataName charset utf8;

（2）删除数据库

drop database dataName;

2、表操作

（1）新建表

create table if not EXISTS tableName(

testName varchar(10),id int(2)

) charset utf8;

（2）修改表

基本语法：alter table + 表名 + 表选项[=] + 值;

（1）新增字段：ALTER TABLE tableName ADD COLUMN idTest INT FIRST;

（2）删除字段：alter table tableName drop idTest;

（3）删除表

DROP TABLE tableName

3、数据操作

（1）插入数据：INSERT INTO tableName VALUES('1','2');

（2）修改数据：UPDATE tableName SET id = '1' WHERE id = '2'

（3）删除数据：DELETE from tableName where id='1'

4、校对集，其实就是数据查询时的排序方式。

• _bin：binary，二进制比较，区分大小写；
• _cs：case sensitive，大小写敏感，区分大小写；
• _ci：case insensitive，大小写不敏感，不区分大小写。（一般新建数据库时都选择这种，utf8_general_ci）

5、数据类型：

 

（1）日期时间型

• datetime：日期时间，其格式为yyyy-MM-dd HH:mm:ss，表示的范围是从 1000 年到 9999 年，有零值，即0000-00-00 0000:00；
• date：日期，就是datetime的date部分；
• time：时间，或者说是时间段，为指定的某个时间区间之间，包含正负时间；
• timestamp：时间戳，但并不是真正意义上的时间戳，其是从1970年开始计算的，格式和datetime一致；
• year：年份，共有两种格式，分别为year(2)和year(4).

6、数据查询

（1）having子句只能与group by联合使用

（2）limit语句查询方式：limit 11,20;  指的是从第11条开始，查询出20条数据

(3)left join和inner join，根据左侧关联表进行查询，left join是查出左侧全部数据，inner join是查出符合条件的数据。

   其实inner join和where的用法类似，但是inner join效率更高，但是一般数据库会将where转为inner，所以看不出效果。

（4）去除重复数据的方法：SELECT * FROM s_user WHERE userId IN (SELECT MAX(userId) FROM s_user GROUP BY position);

通过group by 查出重复的最大的userId，然后查出不重复的所有数据。

（5）联合查询：适用于数据大分表查询和单表但是查询条件不一致时

 

• 查询同一张表，例如查询学生信息，要求男生按年龄升序排序，女生按年龄降序排序；
• 多表查询，多张表的结构是完全一样的，保持的数据结构也是一样的。
• SELECT * FROM s_user UNION SELECT * FROM s_user

 

7、视图

（1）视图可以节省 SQL 语句，将一条复杂的查询语句用视图来进行封装，以后可以直接对视图进行操作；

（2）视图可以更好（或者说，容易）的进行权限控制。

8、触发器

trigger，是指事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行。

 

9、case when的使用：根据查询出来的某个条件得到想要的值

SELECT
    *, CASE gender
WHEN '0' THEN
    '男'
WHEN '1' THEN
    '女'
ELSE '不男不女'
END AS caseName
FROM
    s_user

10 while do循环语句

上面讲的case when只是在查询的时候可以改变查询的值，如果我们想在sql语句中有循环的话就需要用到while do了，在sql中写循环，首先需要定义触发器，通过触发器的方式来执行。虽然感觉很少会有人在sql中写循环，但是还是记录一下方便。

 

delimiter $                                  /*(意思是mysql语句的结尾换成以$结束,mysql默认是;结束)*/
DROP PROCEDURE IF EXISTS `fortest` ;         /*首先需要先删除指定触发器,因为每次执行都需要创建*/

CREATE PROCEDURE fortest ()                   /*创建触发器,里面可以写参数如：fortest(in n int)*/
BEGIN                                         /*开始执行代码语句*/
    DECLARE num,i INT ;                       /**声明变量*/
    SET num = (SELECT count(*) FROM temp),    /*对声明的变量进行赋值*/
    i = 1 ;
WHILE i <= num DO                              /*写while循环语句 do后面写具体的执行方法，我这里写的是插入一条数据，如果主键重复则更新年龄字段*/
    /*取出temp表中第一条记录的key值*/
    INSERT INTO student (id,student.`name`,age,position
    )(SELECT id,temp.`name`,temp.age,temp.position
        FROM temp ORDER BY id LIMIT i,1
    ) ON DUPLICATE KEY
UPDATE student.age = (SELECT age FROM temp ORDER BY id LIMIT i,1) ;
SET i = i + 1 ;                                  /*对循环的i进行加1*/
END
WHILE ;                                         /*当不符合条件时，结束while循环*/
END$                                             /* 存储过程结束*/
delimiter ;                                      /*执行方法*/
CALL fortest ();

11、IF Else语句

完全可以通过case when实现，如果实现想用，基本语法是 if then  事物  else 事物  end if

  WHILE j <= num DO
    SET studentid = (SELECT id from student ORDER BY id LIMIT j,1);
    SET ifhas=(SELECT COUNT(*) from tempred where tempRed.id = studentid);
IF ifhas=0 THEN
    UPDATE student SET student.position ='已恢复2'
    where student.id=studentid;
      SET j=j+1;
ELSE SET j=j+1;
END IF;
  END WHILE ;

12、注意的问题

当在存储特别大的数据时，text可以存放65535个字节，而更大的数据则需要用到longtext来存放，但是在备份和运行sql语句时会报错，需要对数据库进行配置来确定

SELECT @@global.max_allowed_packet;
SET GLOBAL max_allowed_packet=524288000;