CREATE TABLE table_name (
    属性名 数据类型 [完整性约束条件],
    属性名 数据类型 [完整性约束条件],
    .....
    属性名 数据类型 [完整性约束条件],
);

use db_book;
create table t_bookType(
    id int primary key auto_increment,
    bookTypeName varchar(20),
    bookTypeDesc varchar(200)
);

create table t_book(
    id int primary key auto_increment,
    bookname varchar(20),
    author varchar(10),
    price decimal(6,2),
    bookTypeId int,
    constraint `fk` foreign key(`bookTypeId`) references `t_booktype`(`id`)
);

create table Connect(
    con DOUBLE,
    nectid INT
);

alter table Connect add constraint `fkid` foreign key(`nectid`) references `t_booktype`(`id`)

desc t_booktype;
show create table t_booktype;
show tables；

#加到最前面一列
alter table t_book add testField varchar(10) first;
#加到指定属性后面
alter table t_book add testField varchar(10) after author;

create table t_student(
    id int primary key auto_increment,
    stuName varchar(60),
    age int,
    sex varchar(30),
    gradeName varchar(60)
);

insert into db_book.t_student (id,stuName,age,gender,gradeName) values (1,'张一',18,'男','大一');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (2,'张二',19,'男','大二');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (3,'张三',20,'男','大三');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (4,'张四',21,'男','大四');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (5,'张五',22,'男','大一');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (6,'张六',23,'男','大二');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (7,'张七',18,'男','大三');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (8,'张八',20,'男','大四');

insert into db_book.t_student (id,stuName,age,gender,gradeName) values (9,'张九',20,'女','大二'),(10,'张十',20,'女','大四');

#select 字段1,字段2,字段3... from 表名;
select id,stuName,age,gender,gradeName from t_student; #顺序可调
#select * from 表名;
select * from t_student;

select 指定字段1,指定字段3... from 表名;
select stuName,gender from t_student;

#select 字段1,字段2... from 表名 where 条件表达式;
select * from t_student where id=1;
select * from t_student where age>19;

delete from t_student where id=4;

#select 字段1,字段2,字段3... from 表名 where 字段 [NOT] IN (元素1.元素2…)；
select * from t_student where age in (18,19); #只能取这两个，不是范围
select * from t_student where age not in (18,19);

#select 字段1,字段2,字段3... from 表名 where 字段 [NOT] BETWEEN 取值a AND 取值b；
select * from t_student where age between 18 and 20； #18 19 20 都可以取，是范围
select * from t_student where age not between 18 and 20；

#select 字段1,字段2,字段3... from 表名 where 字段 [NOT] LIKE ‘字符串’;
#"%"代表任意字符 '_'代表单个字符，前后都可

select * from t_student where stuName like '张三';
select * from t_student where stuName like '张三%'; #带'张三'开头的
select * from t_student where stuName like '张三_'; #_只能占一个位。多个就写多个_ _
select * from t_student where stuName like '%张三%';

#select 字段1,字段2,字段3... from 表名 where 字段 IS [NOT] NULL;

select * from t_student where age is NULL;
select * from t_student where age is NOT NULL;

#select 字段1,字段2,字段3... from 表名 where 条件表达式1 AND 条件表达式2 AND 条件表达式3 AND ...
#注意末尾是没有分号的

select * from t_student where age=20 and gradeName='大四'

#select 字段1,字段2,字段3... from 表名 where 条件表达式1 OR 条件表达式2 OR 条件表达式3 OR ...
#或，语句末尾也是无分号；

select * from t_student where age=20 or gradeName='大四'

#select distinct 字段名 from 表;

select distinct age from t_student;

#select 字段1,字段2,... from 表名 order by 属性名 [ASC|DESC]
#默认升序

select age from t_student order by age asc;

#按age分组，看每个(岁数)组有哪些stuName,GROUP_CONCAT把所有stuName拼接
select age,GROUP_CONCAT(stuName) from t_student group by age;

#按age分组，看每个(岁数)组有多少个stuName
select age,COUNT(age) from t_student group by age;

#按age分组，看每个(岁数)组有多少个stuName，只输出统计总数大于1的
select age,COUNT(age) from t_student group by age having count(age)>1;

#按age分组，看每个(岁数)组有多少个stuName，多加一行计算总和，若是文本为所有元素拼接
select age,COUNT(age) from t_student group by age with rollup;

#select 字段1,字段2... from 表名 limit 初始位置,记录数,

select * from t_student LIMIT 0,5; #从第0条开始查5条
select * from t_student LIMIT 5,5; #从第5条开始查5条

create table t_garde(
    int primary key auto_increment,
    stuName varchar(60),
    course varchar(50),
    score int
);

insert into t_garde (id,stuName,course,score) values
    (1,'张一一','语文',88),
    (2,'张一一','数学',89),
    (3,'张一一','英语',90),
    (4,'张三三','语文',92),
    (5,'张三三','数学',93),
    (6,'张三三','英语',94),
    (7,'张五五','语文',76),
    (8,'张五五','数学',78),
    (9,'张五五','英语',80);

select COUNT(*) from t_grade; #统计t_grade表内总共有多少条目
select COUNT(*) as total from t_grade; #统计t_grade表内总共有多少条目,并把列名 count(*) 改为 total
select stuName,COUNT(*) from t_grade group by stuName; #按stuName分组，看每个(stuName)组有多少条

select stuName,SUM(score) from t_grade where stuName='张一一'; #求某个学生的总分
select stuName,SUM(score) from t_grade group by stuName; #按stuName分组，看每个(stuName)组的各score总和

select stuName,AVG(score) from t_grade where stuName='张一一'; #求某个学生的总分
select stuName,AVG(score) from t_grade group by stuName; #按stuName分组，看每个(stuName)组的平均score

select stuName,course,MAX(score) from t_grade where stuName='张一一'; #求某个学生的最高score,并列出course
select stuName,MAX(score) from t_grade group by stuName; #按stuName分组，看每个(stuName)组的最高score #分组前面属性只能写一个属性

select stuName,course,MIN(score) from t_grade where stuName='张一一'; #求某个学生的最低score,并列出course
select stuName,MIN(score) from t_grade group by stuName; #按stuName分组，看每个(stuName)组的最低score #分组前面属性只能写一个属性

select * from t_book,t_bookType; #数量取两表笛卡尔乘积(两两组合)

select * from t_book,t_bookType where t_book.bookTypeId=t_booktype.id; #数量取两表笛卡尔乘积后，看bookTypeId与id相等的条目

select bookName,author,bookTypeName from t_book,t_bookType where t_book.bookTypeId=t_booktype.id;

select tb.bookName,tb.author,tby.bookTypeName from t_book tb,t_bookType tby where tb.bookTypeId=tby.id; #设置别名，防止多表字段名有重合

select 属性列表 from 表名1 left|right join 表名2 on 表1.属性1=表2.属性2
#left连接把表1所有属性列出来加上表2匹配的条目，right连接吧表2所有属性列出来加上表1匹配的条目。空值NULL，on限制条件。

select * from t_book left join t_booktype on t_book.bookTypeId=t_booktype.id; #显示表t_book所有属性

select tb.bookName,tb.author,tby.bookTypeName from t_book tb right join t_bookType tby on tb.bookTypeId=tby.id;
#显示表t_bookType全部属性，推荐使用别名。

select tb.bookName,tb.author,tby.bookTypeName from t_book tb,t_bookType tby where tb.bookTypeId=tby.id AND price>70;

select * from t_book where bookTypeID [NOT] in (select id in t_booktype);
#选择表t_book的所有属性显示，筛选bookTypeID 在表t_booktype里的

select * from t_book where price >= (select price from t_pricelevel where pricelevel=1);
#后面括号筛选完成的条件要是数字，不是集合

select * from t_book where [NOT] EXITS (select * from t_booktype);
#后面括号筛选得到空，则不执行前面的查询

select * from t_book where price >= any (select price from t_pricelevel);
#后面括号筛选完成的条件是集合

select * from t_book where price >= all (select price from t_pricelevel);
#后面括号筛选完成的条件是集合

select id from t_book; #(1,2,3,4)
select id from t_booktype; #(1,2,3,6)
select id from t_book UNION select id from t_booktype; #(1,2,3,4,6)
select id from t_book UNION ALL select id from t_booktype; #(1,2,3,4,1,2,3,6)

#insert into 表名 values(值1,值2,值3...);
insert into t_book values(NULL,'围城',,'钱钟书',1)；
#自增的属性可以设为NULL

#insert into 表名(属性1,属性2,属性n) values(值1,值2,值3...);
insert into t_book(id,bookName,price,author,bookTypeId) values(NULL,'围城',28,'钱钟书',1)；
insert into t_book(bookName,author) values('围城','钱钟书')； #也可以取指定的属性插入，其他默认为NULL

#insert into 表名[(属性列表)] values (取值列表1),(取值列表2),...(取值列表n);
insert into t_book(id,bookName,price,author,bookTypeId) values (NULL,'围城2',28,'钱钟书',1),(NULL,'围城3',28,'钱钟书',1);

#update 表名 set 属性1 =取值1,属性2=取值2...属性n=取值n where 条件表达式;
update t_book set bookName='围城',price=40 where id=5; #逗号分隔

update t_book set bookName='钱钟书的书' where like '%围%'；
#使用模糊查询，将表中含有 围 字书名的书全部改为 钱钟书的书

#delete from 表名 where [条件表达式]
#条件不是主键可能会报错，需要百度修改数据库模式。

delete from t_book where id=6; #删除一条

delete from t_student where age>=20; #符合条件多条
delete from t_student where age='2%'; #错误，非字符串不能这么匹配

select * from mysql.`innodb_index_stats` a where a.`database_name` = 'db_book';

show index from tableName;

#普通 单列 索引
create table t_u1(id int, uName varchar(20), password varchar(20), INDEX (uName));

#唯一性单列索引  注意对应位置含义
create table t_u2(id int, uName varchar(20), password varchar(20), UNIQUE INDEX (uName));
create table t_u2(id int, uName varchar(20), password varchar(20), UNIQUE INDEX index_uName(uName)); #带别名

#全文单列索引
create table t_u2(id int, uName varchar(20), password varchar(20), FULLTEXT INDEX (uName));

#多列索引，uName,password两列属性指向一个索引
create table t_u3(id int, uName varchar(20), password varchar(20), INDEX index_uName_password(uName,password));

create INDEX index_uName ON t_u4(uName); #新增普通单列索引
create UNIQUE INDEX index_uName ON t_u4(uName); #新增唯一性单列索引
create INDEX index_uName_password ON t_u4(uName,password); #新增多列索引

alter TABLE t_u5 ADD INDEX index_uName(uName); #新增普通单列索引
alter TABLE t_u5 ADD UNIQUE INDEX index_uName(uName); #新增唯一性单列索引
alter TABLE t_u5 ADD INDEX index_uName_password(uName,password); #新增多列索引

#DROP INDEX 索引名 ON 表名;

DROP INDEX index_userName ON t_u5;
DROP INDEX index_userName_password ON t_u5; #删除多列索引

CREATE VIEW v1 AS SELECT * FROM t_book; #用t_book所有字段生成视图v1
SELECT * FROM v1; #查看视图v1的所有的字段属性

CREATE VIEW v2 AS SELECT bookName,price FROM t_book; #用t_book的bookName,price字段生成视图v2
SELECT * FROM v2;

CREATE VIEW v3(b,p) AS SELECT bookName,price FROM t_book; #创建的视图内将bookName和price字段重命名为 b 和 p
SELECT * FROM v3;

create view v4 as (select * from table1) union all (select * from table2); #合并查询

create view v5 as select bookName,bookTypeName from t_book,t_booktype where t_book.bookTypeID=t_booktype.id;
CREATE VIEW v5 AS SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_booktype tby WHERE tb.bookTypeId=tby.id; #从两张取了别名的表里按指定条件创建视图
SELECT * FROM v5;

#在数据库1 目录下创建
create view 数据库1.v as (select * from 数据库1.table1) union all (select * from 数据库2.table2);

#在数据库2 目录下创建
create view 数据库2.v as (select * from 数据库1.table1) union all (select * from 数据库2.table2);

DESC view1; #基本的视图内容信息：字段名、类型等

# 查看状态信息：包含创建时间，一些虚拟状态值
SHOW TABLE STATUS LIKE 'view1'; #视图是虚表，所以没有collation之类的信息
SHOW TABLE STATUS LIKE 't_book'; #t_book是表，有collation之类的信息

SHOW CREATE VIEW view1; #详细信息，包括编码，建表语句等

#视图v1有4个字段，改为只有bookName和price
CREATE OR REPLACE VIEW v1(bookName,price) AS SELECT bookName,price FROM t_book;

#视图v1只有bookName和price，改为t_book所有的字段
ALTER VIEW v1 AS SELECT * FROM t_book;

#视图v1有id,bookName,price,author,bookTypeID
INSERT INTO v1 VALUES(NULL,'java good',120,'feng',1);

UPDATE v1 SET bookName='java very good',price=200 WHERE id=5;

DELETE FROM v1 WHERE id=5;

DROP VIEW IF EXISTS view1;

#创建trigger：在insert插入新数据操作之后 更新书的数量 new表示插入的条目
CREATE TRIGGER trig_book AFTER INSERT
    ON t_book FOR EACH ROW
        UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;

INSERT INTO t_book VALUES(NULL,'java好',100,'ke',1); #插入操作触发trigger

#创建触发器
DELIMITER |
CREATE TRIGGER trig_book2 AFTER DELETE
    ON t_book FOR EACH ROW
    BEGIN
        UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
        INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据'); #mysql里NOW（）表示当前时间
        DELETE FROM t_test WHERE old.bookTypeId=t_test.id; #顺便把t_test的一条也删掉
    END
|
DELIMITER;

DELETE FROM t_book WHERE id=5; #删除操作触发trigger

SHOW TRIGGERS; #列出来所有的

DROP TRIGGER trig_book ;

select now();
select sysdate();

获取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒

set @test='2019-03-26 23:08:12.123456';

select date(@test); 		#示例
+-------------+
| date(@test) |
+-------------+
| 2019-03-26  |
+-------------+

SELECT CURDATE(),CURTIME(),MONTH(birthday) AS m FROM table; #birthday是列名，MONTH(birthday)重命名为m
select curdate(),curtime() from gongkuang limit 5;

SELECT uName,CHAR_LENGTH(uName),UPPER(uName) up,LOWER(uName) low,concat(uName,upper(uName)) FROM t_u1;

select uName,left(uName,2),right(uName,2) from t_u1;

ASCII(str),
BIN(N),
CONV(str,from_base,to_base),
ELT(N,str1,str2,str3,…),
FIELD(str,str1,str2,str3,…),
FIND_IN_SET(str,strlist),
FORMAT(X,D),
INSERT(str,pos,len,newstr),
LOCATE(substr,str,pos),
REPLACE(str,from_str,to_str),
POSITION(substr IN str),
INSTR(str,substr),

select num,ABS(num),SQRT(num),MOD(num,3) from t_t; #计算num列的绝对值和对3求余

#t_t有id,birthday,uName,num,password,pp    其中pp是blob类型
INSERT INTO t_t VALUES(NULL,'2018-11-11','a',1,PASSWORD('123456')); #password字段不是明文123456
INSERT INTO t_t VALUES(NULL,'2018-11-11','a',1,MD5('123456')); #password字段不是明文123456
INSERT INTO t_t VALUES(NULL,'2018-11-11','a',1,MD5('123456'),ENCODE('abcd','aa')); #pp是明文abcd用aa加密得到的二进制
SELECT DECODE(pp,'aa') FROM t_t WHERE id=1; #用aa解密pp 得到abcd

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

sp_name 参数是存储过程/或存储函数的名称

proc_parameter 表示存储过程的参数列表，每个参数格式 [IN|OUT|INOUT] param_name type
    IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；param_name 参数是
    存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型

func_parameter:param_name type

type:Any valid MySQL data type

characteristic 参数指定存储过程的特性
可取:LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }| COMMENT 'string'

    LANGUAGE SQL：说明 routine_body 部分是由 SQL 语言的语句组成

    [NOT] DETERMINISTIC  存储过程输出结果是否是确定的，即每次输入一样输出也一样的程序，NOT 每次输入一样但输出可能不一样

    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER } 指明子程序使用 SQL 语句的限制
        - CONTAINS SQL子程序包含 SQL 语句，但不包含读或写数据的语句。默认格式
        - NO SQL 表示子程序中不包含 SQL语句
        - READS SQL DATA 子程序中包含读数据的语句
        - MODIFIES SQL DATA 表示子程序中包含写数据的语句

    SQL SECURITY { DEFINER | INVOKER }；指明谁有权限来执行。
        - DEFINER 表示只有定义者自己才能够执行；
        - INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是 DEFINER。

    COMMENT ‘string’ ：注释信息；

routine_body 参数是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束

DELIMITER &&
CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT)
    READS SQL DATA
    BEGIN
         SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT;
    END
&&
DELIMITER ;

CALL pro_book(1,@total); #调用存储过程

DELIMITER &&
CREATE FUNCTION func_book (bookId INT)
    RETURNS VARCHAR(20)
    BEGIN
        RETURN ( SELECT bookName FROM t_book WHERE id=bookId );
    END
&&
DELIMITER ;

SELECT func_book(2); #调用存储函数

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
#characteristic:参数含义类似

#DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

DROP PROCEDURE pro_user3;

DECLARE var_name[,...] type [DEFAULT value]