mysql 山东培训记

MySQL课堂笔记

（201908山东培训记）
注：此文仅为个人学习笔记
链接：https://pan.baidu.com/s/1Ua4u84PTF9-WVZlM4JNP4Q
提取码：znzd

目录

一、mysql操作
二、数据库操作
三、 数据类型
四、SQL操作
五、关键字
六、各种功能
七、函数
八、流程控制
九、视图
十、触发器
十一、索引

一 、mysql操作

1. 命令行操作

 mysql -uroot -p******-h 
-u 用户名
-p 密码
-h 主机地址 本地 127.0.0.1
-d 数据库
-P 端口号

2. 退出 exit 或 quit

启动 关闭 mysql 服务
A: net start MySQL57
B: net stop MySQL57

3. mysql操作

1. SELECT VERSION(); # 获取当前版本信息
2. SELECT NOW(); # 获取时间信息
3. SELECT USER(); # 获取用户信息
4. SHOW DATABASES; # 显示数据库
5. USE mysql; # 使用mysql数据库
6. SHOW TABLES; # 显示数据库下所有的表
7. SELECT * FROM USER; # 从user表查询数据

二、数据库操作

1. 查看当前正在使用的数据库 SELECT DATABASE();
2. 创建数据库 CREATE DATABASE test;
3. 删除数据库 DROP DATABASE test;
4. 创建表
   CREATE TABLE tb_user
   {
   user_id INT NOT NULL AUTO_INCREMENT,
   user_name CHAR(50),
   PRIMARY KEY(user_id)
   }
   SELECT * FROM tb_user;

三、数据类型

1.整型浮点型

          大小（字节）     范围        用途

1. tinyint 1 -128,127 小整型值
2. smallint 2
3. mediumint 3
4. int 4
5. bigint 8
6. float 4 浮点型，单精度
7. double 8 双精度，浮点
8. decimal(m,d) 精确 小数

2. 字符类型

1. char 0-255 定长字符串
2. varchar 0-255 变长字符串
3. blob 0-65535 二进制长文本数据
4. text 0-65535 长文本数据

3. 日期类型

1. date 3 日期值
2. time 3 时间值
3. year 1
4. datetime 8
5. timestamp 8 时间戳 1970年1月1日~~~2037年

4. 属性等其他要求

1. 主键 prmary key(xxx, xx)
2. 唯一 不能重复
3. 可以是一个列或多个列的组合
4. 主键不能为null
5. auto_increment 自动增长
6. 一个表中只允许一个自动增长列
7. 获取自动增长列的值
   SELECT LAST_INSERT_ID();
   SELECT * FROM tb_user;
8. not null 非空字段
9. 修改数据库引擎 ENGINE=MYISAM
10. DEFAULT 默认值

四、SQL操作

1. 添加列字段 默认添加到最后
   ALTER TABLE tb_user ADD COLUMN address VARCHAR(50)
   NOT NULL DEFAULT ‘addr’ COMMENT ‘地址’;
   SELECT * FROM tb_user;

2. 添加到最开始 first
   ALTER TABLE tb_user ADD COLUMN u_id INT FIRST;

3. 添加到某个位置
   ALTER TABLE tb_user ADD COLUMN sex INT AFTER user_name;

4. 查看表字段信息
   DESC tb_user;

5. 修改某个列的属性 MODIFY 、MODIFY COLUMN
   ALTER TABLE tb_user MODIFY u_id BIGINT NOT NULL;
   SELECT * FROM tb_user;

6. 修改列名 CHANGE
   ALTER TABLE tb_user CHANGE u_id uid INT;

7. 修改表名 RENAME TO
   ALTER TABLE tb_user RENAME TO t_user;

8. 删除列 DROP COLUMN
   ALTER TABLE t_user DROP COLUMN uid

9. 删除表 DROP TABLE
   USE mysql;
   DROP TABLE tb_user;

10. 删除表中的数据 ，自动增长列数据还是会起作用
    DELETE FROM tb_user;
    TRUNCATE tb_user;
    SELECT * FROM tb_user;

11. 插入数据
    INSERT INTO tb_user(user_name)
    VALUES(‘zhangsan’);
    INSERT INTO tb_user(user_name)
    VALUES(‘lisi’),(‘wangwu’);

12. 获取最后一次插入数据id值
    SELECT LAST_INSERT_ID();

13. as 起别名
    SELECT LAST_INSERT_ID() AS lastid;

14. 修改数据
    UPDATE tb_user SET user_name=‘abc’ WHERE user_id=1;
    SELECT * FROM tb_user;

15. 删除表中数据
    DELETE FROM tb_user WHERE user_id=1;

16. TRUNCATE 不能有where语句
    TRUNCATE 不能有外键关联
    TRUNCATE 更高效
    TRUNCATE tb_user WHERE user_id=2; # 会报错

17. 排序 升序ASC 降序DESC ORDER BY
    SELECT * FROM tb_user ORDER BY address DESC,sex ASC;

18. count(*) count(列名) 不包含null值
    SELECT COUNT(user_name) AS total FROM tb_user;

19. 获取最大值
    SELECT MAX(user_id) FROM tb_user;

20. 分组查询
    SELECT user_id,user_name,address FROM tb_user
    GROUP BY address;

21. 求和
    SELECT SUM(user_id),address FROM tb_user
    GROUP BY address;

22. 求平均数
    SELECT AVG(user_id),address FROM tb_user
    GROUP BY address;

23. where 条件
    逻辑运算符 = 等于 <>不等于 ！= <= >=
    关系运算符 ： and or not
    SELECT * FROM tb_user;
    SELECT * FROM tb_user WHERE sex = 2;
    SELECT * FROM tb_user WHERE sex =1 AND address = 1;

24. 去除重复
    SELECT DISTINCT sex FROM tb_user;

25. 分页查询 limit 偏移量，数据数量
    SELECT * FROM tb_user LIMIT 0,5;
    SELECT * FROM tb_user LIMIT 5,5;
    页数 条数 偏移量
    1 5 0-5 0,5 （1-1）*条数，条数
    2 5 6-10 5,5 （2-1）*5,5

26. 获取某个区间的数据
    SELECT * FROM tb_user WHERE user_id BETWEEN 4 AND 7;
    SELECT * FROM tb_user WHERE user_id >= 4 AND user_id<=7;

27. null 值判断
    查询 null字段 ， 不能用= ！=
    SELECT * FROM tb_user WHERE user_name IS NULL;
    SELECT * FROM tb_user WHERE user_name IS NOT NULL;

28. and 的优先级高于 or ，可以通过添加括号提升优先级
    SELECT * FROM tb_user WHERE (user_id >3 OR sex > 0 )
    AND address != 2;

29. (not)in (不)在…范围内
    SELECT * FROM tb_user WHERE user_id IN (4,6,8,9);
    SELECT * FROM tb_user WHERE user_id NOT IN (4,6,8,9);
    SELECT * FROM tb_user WHERE user_id
    IN (SELECT user_id FROM tb_user WHERE sex=2);

下午

五、SQL关键字

（1）like

1. like 模糊查询
   like 通配符；
   _ 下划线通配符： 匹配一个字符
   % 匹配一个或多个任意字符

SELECT * FROM tb_user WHERE user_name LIKE ‘_bc’;
SELECT * FROM tb_user WHERE user_name LIKE ‘b_c%’;

注意：不要过度使用like
不要把like放在where句子的开始位置
通过like创建的表，没有原表数据，有主键约束
CREATE TABLE t_user LIKE tb_user;

通过其他表来创建，内容、数据与原来一致，但没有主键约束
CREATE TABLE tt_user AS SELECT * FROM tb_user;

SELECT * FROM tt_user;
SELECT * FROM tb_user;

（2） 连接符 concat

SELECT CONCAT(user_name,’【性别：’,sex,’】’) AS ‘用户信息’ FROM tb_user;

（3）运算符 + - * / %

SELECT user_name,sex, address, sex + address FROM tb_user;

（4）聚合函数 max min avg count

SELECT MIN(address) FROM tb_user; # 忽略null行
SELECT AVG(address) FROM tb_user; # 忽略null行

（5）having 用于对 group by 语句进行过滤

SELECT * FROM tb_user GROUP BY sex HAVING address > 1;

（6）where 用户分组前的过滤

SELECT * FROM tb_user WHERE user_name LIKE ‘%bc%’
GROUP BY sex HAVING address > 1;

（7）汇总

1.select
2. from
3. where
4. group by
5. having
6. order by
7. limit

六、各种功能

1. 重置 AUTO_INCREMENT
   ALTER TABLE 表名 AUTO_INCREMENT = 1;

2. 查询某个范围之内
   SELECT * FROM tb_user WHERE address IN (2,3);

3. 多条件查询
   SELECT * FROM tb_user WHERE address = 2 OR address = 3;

4. in 和 or ，
   如果所在列有索引或主键，效率没啥差别，执行时间几乎一样
   如果没有索引，性能差别较大
   随着数据量的增多，in的效率不会下降太大
   or会随着记录越多，性能受影响越大。

5. 多表关联查询
   SELECT * FROM tb_order;
   SELECT * FROM tb_user;
   SELECT u.user_name,o.name,o.num,o.user_id
   FROM tb_user u , tb_order o
   WHERE u.user_id = o.user_id;

6. 用查询结果集作为关联表
   SELECT a.user_name,a.name AS ‘产品’,c.tel,c.address,a.num,a.user_id
   FROM tb_contect c,
   (SELECT u.user_name,o.name,o.num,o.user_id
   FROM tb_user u , tb_order o
   WHERE u.user_id = o.user_id) a
   WHERE c.user_id = a.user_id;

7. if exists 判断是否存在
   if not exists
   DROP TABLE IF EXISTS users;
   SHOW WARNINGS;

8. having
   SELECT * FROM tb_user WHERE user_id > 5
   GROUP BY sex HAVING SUM(address) > 1;

9. 错误写法 select 中必须包含 having后面的列
   SELECT user_id FROM tb_user WHERE user_id > 5
   GROUP BY sex HAVING address > 1;

10. unique key 唯一

CREATE TABLE tc_user(
user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(20) NOT NULL UNIQUE KEY,
age TINYINT UNSIGNED
);

INSERT INTO tc_user VALUES(NULL,‘a’,18);
SELECT * FROM tc_user;

10. 不能插入 UNIQUE 字段相同的数据
    INSERT INTO tc_user VALUES(NULL,‘a’,19);

11. 约束
    primary key 主键约束
    not null 非空约束
    default 默认约束
    unique key 唯一约束
    foreign key 外键约束， 保证数据完整性
    外键约束要求
    (1). 父表和子表要有相同的存储引擎，InnoDB
    (2). 禁止使用临时表
    (3). 外键列和参照列，数据类型一样，有无符号位必须相同
    (4). 外键列和参照列必须有索引，mysql会自动创建

CREATE TABLE province(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
)

CREATE TABLE puser(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
pid INT UNSIGNED,
CONSTRAINT fk_pid_id FOREIGN KEY(pid) REFERENCES province(id)
)

12. 查看创建表的信息
    SHOW CREATE TABLE puser;

13. 查看索引信息
    SHOW INDEXES FROM puser;
    INSERT INTO puser VALUES(NULL,‘jason’,1);
    SELECT * FROM puser;
    INSERT INTO puser VALUES(NULL,‘jason’,3);
    DELETE FROM province WHERE id = 1;
    TRUNCATE puser;

14. 外键约束的一些操作
    (1).CASCADE : 从父表中删除或更新会
    自动删除或更新子表内容
    (2).SET NULL: 会自动设置子表为nuLL
    前提：字表中对应字段没有设为not null
    (3).RESTRICT: 拒绝父表的更新或删除的操作
    (4).NO ACTION: 与RESTRICT相同，是SQL关键字
    在没有设置这些操作时：
    需要将字表中对应的数据删除后，才可以删除父表数据

CREATE TABLE puser1(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
pid INT UNSIGNED,
CONSTRAINT fk_pid_id1 FOREIGN KEY(pid)
REFERENCES province(id) ON DELETE CASCADE
)
SELECT * FROM puser1 FOR UPDATE;
SELECT * FROM province;
DELETE FROM province WHERE id=1;
UPDATE province SET id = 1 WHERE id = 2;

15. 物理外键 逻辑外键
    CREATE TABLE puser1(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    pid INT UNSIGNED COMMENT ‘省份表id，外键’
    )

七、函数

SELECT NOW();
SELECT MD5(‘admin’);

1. 自定义函数
   SELECT DATE_FORMAT(NOW(),’%Y年%m月%d日 %H时%i分%s秒’);

2. 定义无参函数
   CREATE FUNCTION fun1() RETURNS VARCHAR(30)
   RETURN DATE_FORMAT(NOW(),’%Y年%m月%d日 %H时%i分%s秒’);
   SELECT fun1();
   SELECT fun1;

3. 定义有参数的函数
   CREATE FUNCTION fun2(num1 INT, num2 INT)
   RETURNS FLOAT(10,2)
   RETURN num1 + num2;
   SELECT fun2(1,2);

4. 插入数据
   如果函数体中有多条sql语句，用 BEGIN END 包裹
   如果函数体中有sql语句，需要有分号结尾
   需要指定其他结束符号， 比如 DELIMITER //

DELIMITER //
CREATE FUNCTION addProvince(newname VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT province(pname) VALUES(newname);
RETURN LAST_INSERT_ID();
END
//
DELIMITER ;
SELECT addProvince(‘北京’);

5. 函数体，合法的sql语句
   如果是复合结构 ，用 BEGIN END 包裹
   复合结构可以包括： 声明， 循环 ，控制结构

6. 删除函数
   DROP FUNCTION IF EXISTS addProvince;

7. 存储过程

8. sql语句预编译的集合
   CREATE PROCEDURE p1()
   SELECT VERSION();
   CALL p1();
   CALL p1;

9. 参数
   in , 在调用存储过程的时候就需要指定
   out， 返回值
   inout，in 和 out 的结合

10. 带参数存储过程
    DELIMITER $$CREATEPROCEDUREremoveProvince(INnidINT)BEGINDELETEFROMprovinceWHEREid=nid;END$$
    DELIMITER ;
    SELECT * FROM province;
    CALL removeProvince(2);

11. 删除存储过程
    DROP PROCEDURE IF EXISTS removeProvince;

DELIMITER $$CREATEPROCEDUREremoveUser(INnidINT,OUTuserNumINT)BEGINDELETEFROMt{b}_{u}serWHEREuse{r}_{i}d=nid;SELECTCOUNT(use{r}_{i}d)FROMt{b}_{u}serINTOuserNum;END$$
DELIMITER ;

SELECT * FROM tb_user;

SELECT COUNT(user_id) FROM tb_user

CALL removeUser(2,@nums);

12. @nums : 用户变量
    SELECT @nums;

13. 定义用户变量
    SET @i = 1;
    SELECT @i;
    SELECT @a:=1;

14. 局部变量
    DECLARE age INT DEFAULT 0;
    SET age = 18;

15. 会话变量
    SHOW SESSION VARIABLES;
    SET SESSION sess_test = 1;
    SET @@session.sess_test1 = 2;
    SET sess_test3 = 3; #当省略session关键字时，默认缺省为session，即设置会话变量的值

SELECT @@sess_test;
SELECT @@session.sess_test1;
SHOW SESSION VARIABLES LIKE ‘%sess_test%’;

16. session 可以被 local 取代

17. 全局变量
    SHOW GLOBAL VARIABLES;

SET GLOBAL glo_test=1;
SET @@global.glo_test1 = 2;

SELECT @@global.glo_test;
SHOW GLOBAL VARIABLES LIKE ‘%glo%’;

18. 返回受影响的行数
    SELECT ROW_COUNT();
    DELIMITER $$CREATEPROCEDUREremoveUsers(INnidINT,OUTuserNumINT,OUTucountINT)BEGINDELETEFROMt{b}_{u}serWHEREuse{r}_{i}d<nid;SELECTRO{W}_{C}OUNT()INTOucount;SELECTCOUNT(use{r}_{i}d)FROMt{b}_{u}serINTOuserNum;END$$
    DELIMITER ;

CALL removeUsers(6,@a,@b);

SELECT @a,@b;

19. 复合结构
    DELIMITER KaTeX parse error: Expected 'EOF', got '#' at position 196: …'女'; END CASE; #̲return (select …
    DELIMITER ;
    DROP FUNCTION getSex;
    SELECT getSex(2);

20. 流程控制 if then else
    DELIMITER $$CREATEFUNCTIONgetUserName(u_{a}ddressINT)RETURNSVARCHAR(20)BEGINDECLAREuserNameVARCHAR(20);DECLAREnumINT;SELECTCOUNT(\ast )FROMt{b}_{u}seruWHEREu.address=u_{a}ddressINTOnum;IFnum!=0THENSELECTuse{r}_{n}ameFROMt{b}_{u}seruWHEREu.address=u_{a}ddressINTOuserName;ELSESETuserName{=}^{\prime }查无此{人}^{\prime };ENDIF;RETURNuserName;END$$
    DELIMITER ;

SELECT getUserName(1);

21. 存储过程和函数的区别
    函数有返回值，存储过程没有返回值
    存储过程可以使用 IN OUT INOUT
    (OUT INOUT) 可以向外传出数据

DELIMITER //
CREATE PROCEDURE insertUser(
IN in_num TINYINT UNSIGNED,
OUT max_id INT UNSIGNED,
OUT total_count INT UNSIGNED
)
BEGIN
DECLARE i,j INT UNSIGNED;
SELECT MAX(user_id) FROM tb_user INTO i;
SET j = i + in_num;
WHILE i<j DO
INSERT INTO tb_user(user_name)
VALUES(CONCAT(‘刘德华+’,i));
SET i = i + 1;
END WHILE;
SELECT MAX(user_id) FROM tb_user INTO max_id;
SELECT COUNT(*) FROM tb_user INTO total_count;
END//
DELIMITER ;

CALL insertUser(10,@a,@b);

SELECT @a,@b;
SELECT * FROM tb_user;

22. 根据多条件查询
    DROP PROCEDURE IF EXISTS getUser;
    DELIMITER //
    CREATE PROCEDURE getUser(IN info INT)
    BEGIN
    DECLARE presql VARCHAR(200);
    SET presql = CONCAT(‘SELECT * FROM t_user
    WHERE user_id>’,info,
    ’ AND sex=’,info,
    ’ AND address=’,info,’;’);
    SET @s_sql = presql;
    PREPARE stmt FROM @s_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    END//
    DELIMITER ;

DROP PROCEDURE IF EXISTS getUsers;
DELIMITER //
CREATE PROCEDURE getUsers(IN u_id INT,IN sex INT,IN addr INT)
BEGIN
DECLARE presql VARCHAR(200);

SET presql = 'SELECT * FROM t_user WHERE 1=1 ';

IF u_id IS NOT NULL AND u_id!=0 THEN
SET presql = CONCAT(presql,’ AND user_id>’,u_id);
END IF;

IF sex IS NOT NULL AND sex!=0 THEN
SET presql = CONCAT(presql,’ AND sex=’,sex);
END IF;

IF addr IS NOT NULL AND addr!=0 THEN
SET presql = CONCAT(presql,’ AND address=’,addr);
END IF;

SET @s_sql = presql;
PREPARE stmt FROM @s_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

八、流程控制

1. 循环
   loop
   标签名:loop
   leave 标签名 --退出循环
   end loop

DELIMITER //
DROP PROCEDURE IF EXISTS pNum;
CREATE PROCEDURE pNum(IN num INT)
BEGIN
DECLARE n INT DEFAULT 1;
DECLARE s INT DEFAULT 1;
aa:LOOP
SET s = s+n;
SET n = n+1;
IF n > num THEN
LEAVE aa;
END IF;
END LOOP;
SELECT s;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE pNum(IN num INT)
BEGIN
DECLARE n INT DEFAULT 1;
DECLARE s INT DEFAULT 0;
aa:LOOP
SET s=s+n;
SET n=n+1;
IF n>num THEN
LEAVE aa;
END IF;
END LOOP;
SELECT s;
END//
DELIMITER ;

CALL pNum(10);

2. repeat 循环

repeat

代码

until 条件 end repeat

DELIMITER //
CREATE PROCEDURE rNum(num INT)
BEGIN
DECLARE n INT DEFAULT 1;
DECLARE s INT DEFAULT 0;
REPEAT
SET s=s+n;
SET n=n+1;
UNTIL n>num END REPEAT;
SELECT s;
END//
DELIMITER ;

CALL rNum(100);

九、视图 view

1. 视图是查询结果，表改变，视图改变
   视图中的数据与原表一一对应，可以修改视图内容
   insert时注意：视图中需要包含表中没有默认值的列
   视图一般只用来查询，尽量避免增删改操作

CREATE VIEW v_user
AS SELECT user_name,sex,address
FROM tb_user;

SELECT * FROM v_user;
SELECT * FROM tb_user;

UPDATE tb_user SET sex = 1 WHERE user_id = 9;

UPDATE v_user SET address = ‘abc’ WHERE sex = 0;

INSERT INTO v_user
VALUES(‘abc’,1,‘address’)

2. 视图中如果包含 去重（distinct），group by
   order by, 子查询，多个表
   函数，涉及有计算的列
   有非空约束字段不在视图中的表
   不要进行增删改操作

3. 视图中对应的表中如果没有更新相应的
   非空且无默认值的字段，会报错

4. 别名
   CREATE VIEW v_user
   AS SELECT user_name AS uname,sex,address
   FROM tb_user;

CREATE VIEW v_puser_province
AS SELECT a.username,p.pname
FROM puser a, province p
WHERE a.pid = p.id;

SELECT * FROM v_puser_province;

5. 有外键约束的表，删除需要注意
   DELETE FROM v_puser_province
   WHERE username = ‘jason’;

6. 错误代码： 1395
   Can not delete from join view ‘test.v_puser_province’

7. 删除视图
   drop view if exists 视图名

十、触发器

1. 触发器
   CREATE TABLE tb_logs (
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   logtext VARCHAR(255) DEFAULT NULL COMMENT ‘日志说明’,
   intputdate DATETIME DEFAULT NULL
   ) COMMENT=‘日志表’;

2. 创建触发器
   after before 在。。之前，之后

3. 六种触发器
   after insert before insert
   after update before update
   after delete before delete

DROP TRIGGER IF EXISTS writelog;
DELIMITER //
CREATE TRIGGER writelog
AFTER INSERT ON tb_user FOR EACH ROW
BEGIN
DECLARE logtext VARCHAR(255);
SET logtext = “创建了用户…”;
SET logtext = CONCAT(logtext, NEW.user_name);
INSERT INTO tb_logs(logtext,intputdate)
VALUES(logtext,NOW());
END //
DELIMITER ;

SELECT * FROM tb_logs;
INSERT INTO tb_user VALUES(NULL,‘aaaa’,1,‘bbbb’);
SELECT * FROM tb_user;

DROP TRIGGER IF EXISTS updatelogs;
DELIMITER //
CREATE TRIGGER updatelogs
AFTER UPDATE ON puser FOR EACH ROW
BEGIN
DECLARE logtext VARCHAR(255);
SET logtext = ‘更新了puser…’;
SET logtext = CONCAT(logtext, NEW.username,"–",OLD.username);
INSERT INTO tb_logs(logtext,intputdate)
VALUES(logtext,NOW());
END //
DELIMITER ;

UPDATE tb_user SET user_name = ‘bbbbbbbb’ WHERE user_id = 7;

SELECT * FROM tb_user;
UPDATE tb_user SET sex = 2 WHERE user_id = 6;

DELETE FROM tb_user WHERE user_id = 6;

UPDATE puser SET username = ‘rrrrr’ WHERE id = 2;

SELECT * FROM puser;

SELECT * FROM tb_logs;

4. ‘test.v_user’ is not BASE TABLE

DROP TRIGGER IF EXISTS updatelogs;
DELIMITER //
CREATE TRIGGER updatelogs
AFTER INSERT ON v_user FOR EACH ROW
BEGIN
DECLARE logtext VARCHAR(255);
SET logtext = ‘新增了v_user…’;
SET logtext = CONCAT(logtext, NEW.username);
INSERT INTO tb_logs(logtext,intputdate)
VALUES(logtext,NOW());
END //
DELIMITER ;

5. 触发须定义在表上
   自动执行，不需要调用

6. 要素
   监视的地点： table
   监视事件： insert update delete
   触发时间： before after

十一、索引

SHOW INDEX FROM province;
SHOW INDEX FROM puser;

ALTER TABLE puser
ADD INDEX username_index (username);

ALTER TABLE puser
ADD INDEX username_pid_index (username,pid);

(1). 可以命中索引
SELECT * FROM puser WHERE username = ‘xx’ AND pid = 1;
(2). 可以命中索引
SELECT * FROM puser WHERE username = ‘xx’;
(3). 无法命中索引
SELECT * FROM puser WHERE pid = 1;

1.添加主键索引
alter table 表名 add primary key (列名);

2.添加唯一索引
alter table 表名 add unique (列名);

3.添加普通索引
alter table 表名 add index 索引名(列名);

4.添加全文索引
alter table 表名 add fulltext(列名);

5.添加多列索引
alter table 表名 add index(列名1,列名2…);
SELECT * FROM tb_user;

下午====
/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.7.10-log : Database - test

---

*/

/*!40101 SET NAMES utf8 */;

/!40101 SET SQL_MODE=’’/;

/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;
CREATE DATABASE /!32312 IF NOT EXISTS/test /*!40100 DEFAULT CHARACTER SET utf8 */;

USE test;

/*Table structure for table province */

DROP TABLE IF EXISTS province;

CREATE TABLE province (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
pname varchar(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table province */

insert into province(id,pname) values (3,‘北京’),(4,‘山东’);

/*Table structure for table puser */

DROP TABLE IF EXISTS puser;

CREATE TABLE puser (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
username varchar(20) NOT NULL,
pid int(10) unsigned DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_pid_id (pid),
KEY username_index (username),
KEY username_pid_index (username,pid),
CONSTRAINT fk_pid_id FOREIGN KEY (pid) REFERENCES province (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table puser */

insert into puser(id,username,pid) values (3,‘lily’,4),(2,‘rrrrr’,3);

/*Table structure for table puser1 */

DROP TABLE IF EXISTS puser1;

CREATE TABLE puser1 (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
username varchar(20) NOT NULL,
pid int(10) unsigned DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_pid_id1 (pid),
CONSTRAINT fk_pid_id1 FOREIGN KEY (pid) REFERENCES province (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table puser1 */

/*Table structure for table t_user */

DROP TABLE IF EXISTS t_user;

CREATE TABLE t_user (
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name char(50) DEFAULT NULL,
sex int(11) DEFAULT NULL,
address varchar(50) DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table t_user */

/*Table structure for table tb_contect */

DROP TABLE IF EXISTS tb_contect;

CREATE TABLE tb_contect (
contect_id int(10) unsigned NOT NULL AUTO_INCREMENT,
tel varchar(50) DEFAULT NULL,
address varchar(50) DEFAULT NULL,
user_id int(11) DEFAULT NULL,
PRIMARY KEY (contect_id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Data for the table tb_contect */

insert into tb_contect(contect_id,tel,address,user_id) values (1,‘123’,‘2222222’,2),(2,‘456’,‘33333333’,3);

/*Table structure for table tb_logs */

DROP TABLE IF EXISTS tb_logs;

CREATE TABLE tb_logs (
id int(11) NOT NULL AUTO_INCREMENT,
logtext varchar(255) DEFAULT NULL COMMENT ‘日志说明’,
intputdate datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT=‘日志表’;

/*Data for the table tb_logs */

insert into tb_logs(id,logtext,intputdate) values (1,‘创建了用户…’,‘2019-09-03 09:35:28’),(2,‘创建了用户…’,‘2019-09-03 09:36:10’),(3,‘创建了用户…’,‘2019-09-03 09:36:10’),(4,‘创建了用户…’,‘2019-09-03 09:36:10’),(5,‘创建了用户…’,‘2019-09-03 09:36:10’),(6,‘创建了用户…’,‘2019-09-03 09:36:11’),(7,‘创建了用户…’,‘2019-09-03 09:36:11’),(8,‘创建了用户…’,‘2019-09-03 09:36:11’),(9,‘创建了用户…’,‘2019-09-03 09:36:11’),(10,‘创建了用户…’,‘2019-09-03 09:36:11’),(11,‘创建了用户…’,‘2019-09-03 09:36:11’),(12,‘创建了用户…’,‘2019-09-03 09:36:12’),(13,‘创建了用户…’,‘2019-09-03 09:36:12’),(14,‘创建了用户…aaaa’,‘2019-09-03 09:56:03’),(15,‘更新了puser…rrrrr–abc’,‘2019-09-03 10:16:22’),(16,‘创建了用户…张三’,‘2019-09-03 17:37:13’);

/*Table structure for table tb_order */

DROP TABLE IF EXISTS tb_order;

CREATE TABLE tb_order (
order_id int(10) unsigned NOT NULL AUTO_INCREMENT,
num int(11) DEFAULT NULL,
user_id int(11) DEFAULT NULL,
name varchar(50) DEFAULT NULL,
tel varchar(50) DEFAULT NULL,
desc varchar(50) DEFAULT NULL,
PRIMARY KEY (order_id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table tb_order */

insert into tb_order(order_id,num,user_id,name,tel,desc) values (1,5,2,‘apple’,‘123’,‘miaoshu’),(2,2,3,‘电扇’,‘456’,‘电风扇’),(3,1,3,‘电视机’,‘456’,‘大彩电’);

/*Table structure for table tb_user */

DROP TABLE IF EXISTS tb_user;

CREATE TABLE tb_user (
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name char(50) DEFAULT NULL,
sex int(11) DEFAULT NULL,
address varchar(50) DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;

/*Data for the table tb_user */

insert into tb_user(user_id,user_name,sex,address) values (7,‘bbcdfas’,0,‘abc’),(8,‘张三’,1,‘address’),(9,‘刘德华+8’,1,NULL),(10,‘刘德华+9’,NULL,NULL),(11,‘刘德华+10’,NULL,NULL),(12,‘刘德华+11’,NULL,NULL),(13,‘刘德华+12’,NULL,NULL),(14,‘刘德华+13’,NULL,NULL),(15,‘刘德华+14’,NULL,NULL),(16,‘刘德华+15’,NULL,NULL),(17,‘刘德华+16’,NULL,NULL),(18,‘刘德华+17’,NULL,NULL),(19,‘abc’,NULL,NULL),(20,‘abc’,1,‘address’),(21,‘aaaa’,1,‘bbbb’),(22,‘aaaa’,1,‘bbbb’),(23,‘aaaa’,1,‘bbbb’),(24,‘aaaa’,1,‘bbbb’),(25,‘aaaa’,1,‘bbbb’),(26,‘aaaa’,1,‘bbbb’),(27,‘aaaa’,1,‘bbbb’),(28,‘aaaa’,1,‘bbbb’),(29,‘aaaa’,1,‘bbbb’),(30,‘aaaa’,1,‘bbbb’),(31,‘aaaa’,1,‘bbbb’),(32,‘aaaa’,1,‘bbbb’),(33,‘aaaa’,1,‘bbbb’),(34,‘aaaa’,1,‘bbbb’),(35,‘张三’,1,‘山东省’);

/*Table structure for table tc_user */

DROP TABLE IF EXISTS tc_user;

CREATE TABLE tc_user (
user_id int(10) unsigned NOT NULL AUTO_INCREMENT,
user_name varchar(20) NOT NULL,
age tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (user_id),
UNIQUE KEY user_name (user_name)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

/*Data for the table tc_user */

insert into tc_user(user_id,user_name,age) values (1,‘a’,18);

/*Table structure for table tt_user */

DROP TABLE IF EXISTS tt_user;

CREATE TABLE tt_user (
user_id int(11) NOT NULL DEFAULT ‘0’,
user_name char(50) DEFAULT NULL,
sex int(11) DEFAULT NULL,
address varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table tt_user */

insert into tt_user(user_id,user_name,sex,address) values (2,‘abc’,2,‘3’),(3,‘bbc’,2,‘2’),(4,‘dbc’,2,‘3’),(5,‘abc4’,1,‘5’),(6,‘aaaa5’,1,’’),(7,‘bbcdfas’,0,‘2’),(8,‘bsdfd’,1,‘1’);

/* Trigger structure for table tb_user */

DELIMITER $$

/!50003 DROP TRIGGER//*!50032 IF EXISTS / /!50003 writelog */$$

/*!50003 CREATE / /!50017 DEFINER = ‘root’@‘localhost’ / /!50003 TRIGGER writelog AFTER INSERT ON tb_user FOR EACH ROW begin
declare logtext varchar(255);
set logtext = “创建了用户…”;
set logtext = concat(logtext, NEW.user_name);
insert into tb_logs(logtext,intputdate)
values(logtext,now());
end */$$

DELIMITER ;

/* Function structure for function fun1 */

/*!50003 DROP FUNCTION IF EXISTS fun1 */;
DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost FUNCTION fun1() RETURNS varchar(30) CHARSET utf8
RETURN DATE_FORMAT(NOW(),’%Y年%m月%d日 %H时%i分%s秒’) */$$
DELIMITER ;

/* Function structure for function fun2 */

/*!50003 DROP FUNCTION IF EXISTS fun2 */;
DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost FUNCTION fun2(num1 int, num2 int) RETURNS float(10,2)
return num1 + num2 */$$
DELIMITER ;

/* Function structure for function getSex */

/*!50003 DROP FUNCTION IF EXISTS getSex */;
DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost FUNCTION getSex(sex int) RETURNS varchar(5) CHARSET utf8
BEGIN
declare result varchar(5);
case sex
when 0 then set result = ‘未知’;
when 1 then set result = ‘男’;
when 2 then set result = ‘女’;
end case;
#return (select result);
return result;
END */$$
DELIMITER ;

/* Function structure for function getUserName */

/*!50003 DROP FUNCTION IF EXISTS getUserName */;
DELIMITER $$

/!50003 CREATE DEFINER=root@localhost FUNCTION getUserName(u_address int) RETURNS varchar(20) CHARSET utf8
begin
declare userName varchar(20);
declare num int;
select count() from tb_user u
where u.address = u_address into num;
if num!=0 then
select user_name from tb_user u
where u.address = u_address into userName;
else
set userName = ‘查无此人’;
end if;
return userName;
end */$$
DELIMITER ;

/* Procedure structure for procedure insertUser */

/*!50003 DROP PROCEDURE IF EXISTS insertUser */;

DELIMITER $$

/!50003 CREATE DEFINER=root@localhost PROCEDURE insertUser(
in in_num tinyint unsigned,
out max_id int unsigned,
out total_count int unsigned
)
begin
declare i,j int unsigned;
select max(user_id) from tb_user into i;
set j = i + in_num;
while i<j do
insert into tb_user(user_name)
values(concat(‘刘德华+’,i));
set i = i + 1;
end while;
select max(user_id) from tb_user into max_id;
select count() from tb_user into total_count;
end */$$
DELIMITER ;

/* Procedure structure for procedure p1 */

/*!50003 DROP PROCEDURE IF EXISTS p1 */;

DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost PROCEDURE p1()
select version() */$$
DELIMITER ;

/* Procedure structure for procedure pNum */

/*!50003 DROP PROCEDURE IF EXISTS pNum */;

DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost PROCEDURE pNum(IN num INT)
BEGIN
DECLARE n INT DEFAULT 1;
DECLARE s INT DEFAULT 0;
aa:LOOP
SET s=s+n;
SET n=n+1;
IF n>num THEN
LEAVE aa;
END IF;
END LOOP;
SELECT s;
END */$$
DELIMITER ;

/* Procedure structure for procedure removeProvince */

/*!50003 DROP PROCEDURE IF EXISTS removeProvince */;

DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost PROCEDURE removeProvince(IN nid INT,out userNum int)
BEGIN
DELETE FROM tb_user WHERE user_id=nid;
select count(user_id) from tb_user into userNum;
END */$$
DELIMITER ;

/* Procedure structure for procedure removeUser */

/*!50003 DROP PROCEDURE IF EXISTS removeUser */;

DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost PROCEDURE removeUser(IN nid INT,out userNum int)
BEGIN
DELETE FROM tb_user WHERE user_id=nid;
select count(user_id) from tb_user into userNum;
END */$$
DELIMITER ;

/* Procedure structure for procedure removeUsers */

/*!50003 DROP PROCEDURE IF EXISTS removeUsers */;

DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost PROCEDURE removeUsers(IN nid INT,OUT userNum INT,out ucount int)
BEGIN
DELETE FROM tb_user WHERE user_id < nid;
SELECT ROW_COUNT() INTO ucount;
SELECT COUNT(user_id) FROM tb_user INTO userNum;
END */$$
DELIMITER ;

/* Procedure structure for procedure rNum */

/*!50003 DROP PROCEDURE IF EXISTS rNum */;

DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost PROCEDURE rNum(num INT)
BEGIN
DECLARE n INT DEFAULT 1;
DECLARE s INT DEFAULT 0;
REPEAT
SET s=s+n;
SET n=n+1;
UNTIL n>num END REPEAT;
SELECT s;
END */$$
DELIMITER ;

/*Table structure for table v_puser_province */

DROP TABLE IF EXISTS v_puser_province;

/*!50001 DROP VIEW IF EXISTS v_puser_province /;
/!50001 DROP TABLE IF EXISTS v_puser_province */;

/!50001 CREATE TABLE v_puser_province(
username varchar(20) ,
pname varchar(20)
)/;

/*Table structure for table v_user */

DROP TABLE IF EXISTS v_user;

/*!50001 DROP VIEW IF EXISTS v_user /;
/!50001 DROP TABLE IF EXISTS v_user */;

/!50001 CREATE TABLE v_user(
user_name char(50) ,
sex int(11) ,
address varchar(50)
)/;

/*View structure for view v_puser_province */

/*!50001 DROP TABLE IF EXISTS v_puser_province /;
/!50001 DROP VIEW IF EXISTS v_puser_province */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW v_puser_province AS select a.username AS username,p.pname AS pname from (puser a join province p) where (a.pid = p.id) */;

/*View structure for view v_user */

/*!50001 DROP TABLE IF EXISTS v_user /;
/!50001 DROP VIEW IF EXISTS v_user */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW v_user AS select tb_user.user_name AS user_name,tb_user.sex AS sex,tb_user.address AS address from tb_user */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

=完结=================

2019.09.02 数据库基础知识
#mysql -uroot -p***********-h
#-h 主机地址 本地127.0.0.1
#-d 数据库
#-P 端口号

exit quit 退出

#数据库操作
#SELECT VERSION(); #获取当前版本信息
#SELECT NOW(); #获取时间
#SELECT USER(); #获取用户信息
#SHOW DATABASES;
#USE mysql；使用mysql数据库
#SHOW TABLES；显示当前数据库下所有的表
#SELECT * FROM USER; 从user表查询数据
SELECT DATABASE; 查看当前正在使用的数据库
CREATE DATABASE test；创建数据库
DROP DATA test；F9执行
CREATE TABLE tb_uesr 创建表
(
user_id INT NOT NULL auto_INCREMENT,
user_name CHAR(50),
PRIMARY KEY(user_id)
)ENGINE =INNODE db；#修改数据库引擎MYISAM

SELECT * FROM tb_user;

基本数据类型 大小字节 范围 用途
tinyint （byte） 1 -128~127 小整形值
smallint 2
mediumint 3
int 4
bigint 8
float 4 浮点型单精度
double 8 浮点型双精度
decimal(m,d) 精确的小数，更精确m长度d小数位数

字符类型
char 0-255 定长字符串
varchar 0-255 可变长度字符串
blop 0-65535 二进制长文本数据
text 0-65535 长文本数据

日期类型
date 3 日期值
time 3 时间值
year 1 年（不常用）
datetime 8
timestamp 8 时间戳1970年1月1日00点到2037年的数据

主键
PRIMARY KEY(xxx，xxx);至少定义一个主键
主键的特点是唯一的不能重复

auto_increment 自动增长 #一个表中只能有一个自动增长列
SELECT LAST_INSERT_ID();
SELECT * FROM tb_user;
not null #非空字段
修改数据库引擎 ENGINE =MYISAM

=======================================================================
#DEFAULT 默认值
ALTER TABLE tb_user ADD COLUMN address VERCHAR(50) #添加字段，默认添加到最后
NOT NULL DEFAULT ‘adh’COMMENT ‘地址’； #默认添加一个adh字段，用comment描述这个字段，是注释

#添加到最开始
ALTER TABLE tb_user ADD COLUMN u_id INT FIRST(50);
#添加到某个位置
ALTER TABLE tb_user ADD COLUMN sex INT AFTER user_name;#对数据库列的修改，在列后面添加字段
ALTER TABLE tb_user MODIFY u_id BIGINT NOT NULL; #修改列名
ALTER TABLE tb_user CHANGE u_id uid INT ;
ALTER TABLE tb_user RENAME TO t_user; 修改表名
ALTER TABLE tb_user DROP COLUMN uid;删除列
#删除表DROP TABLE
DROP TABLE t_user;

#删除表中的数据
DELETE FROM tb_user;
DELETE FROM tb_user WHERE uer_id =1;
TRUNCATE tb_user #不能有where语句，不能有外键关联，更高效
INSERT INTO tb_user(user_name) VALUES(‘ZHANGSAN’);

================================================
升序ASC 降序DESC ORDER BY
DELETE * FROM tb_user ORDER BY
SELECT user_id,user_name,address FROM tb_user GROUP BY address;

where条件
= <>不等于 ！= and 优先级高于or

分页查询 limit 偏移量，数据数量,从几条开始，查询多少条数据
SELECT * FROM tb_user LIMIT 0,5;
SELECT * FROM tb_user LIMIT 6,20;

in 在···范围的
SELECT * FROM tb_user WHERE user_id IN (4,5,6,9);

=下午==
SELECT CONCAT 连接
having 用户对group by语句进行过滤
主键约束
非空约束 not null
默认约束
外键约束，保证数据的完整性