SQL命令汇总

-- 命令行查看二进制日志
./mysqlbinlog mysql-bin.000008

-- 初始化Mysql
./mysql_secure_installation

-- 修改密码
mysqladmin -u ${USERNAME} password "NEW_PASSWORD";

-- 创建，删除数据库
[create|drop] database DB_NAME;

-- 删表
drop table 表对象；  

-- 清空记录
truncate table TABLE_NAME;
delete from TABLE_NAME [where ...];

-- 查看服务器版本信息
show variables like 'version';
select version();
select now();                   -- 查询时间
select database()		        -- 当前数据库名 (或者返回空)
select user()			        -- 当前用户名

show status			            -- 显示Mysql状态&配置信息

describe TABLE_NAME；           -- 显示表结构
show create table TABLE_NAME;   -- 查看表结构
show engine innodb status\G;    -- 查看某存储引擎使用状态
show table status like 'actor'\G;   -- 查看特定表的状态
show [databases|tables]；       -- 显示所有数据库，表
show columns from TABLE_NAME；  -- 显示表属性，属性的类型，主键信息，是否为 NULL，默认值等其他信息
show index   from TABLE_NAME；  -- 显示表索引，包括PRIMARY KEY
show open tables;               -- 显示当前打开的表
show processlist;               -- 显示当前执行的命令
show global variables;          -- 显示Mysql的配置信息

-- 备份所有数据
mysqldump -u ${USERNAME} -p ${PASSWORD} -A > all_databases.sql

-- 从库中导出表
mysqldump -c -u ${USERNAME} -p ${PASSWORD} DATABASE_NAME TABLE_NAME > DB_NAME.TABLE_NAME.sql

-- 导出到远程服务器
mysqldump -u ${USERNAME} -p DATABASE_NAME | mysql -h 192.168.1.1 -p ${PASSWORD} DB_NAME

-- 建表时先判断表是否存在 
create table if not exists TABLE_NAME;

-------------------------------------------------------------------

/*
    数据库：保存有组织的数据的容器（通常是一或一组文件）
    数据库软件被称为DBMS，数据库是通过DBMS创建和操纵的容器
    模式：关于数据库和表的布局及特性的信息
    主键：一列或一组列，其值能够唯一标识表中的每行，保证一组列是唯一的 
    SQL语句不分大小写，不过表名，列名和值可能有所不同（依赖于具体的DBMS及其如何配置）
    检索多个列，列名之间须以 ，分隔，但最后一个列不用加，第一个检索的行是0行
    limit 4 offset 3 简化版：limit 3,4
    检索不同的值使用 DISTINCT 关键字放在列名前，NOT WHERE 子句用来否定其后条件关键字
*/
------------------------------------------------------------------- SQL执行顺序

1. from 
3. join
2. on
4. where -- 开始使用select中的别名，后面的语句中都可以使用
5. group by
6. avg,sum .... 
7. having
8. select
9. distinct
10. order by

-- 所有的查询语句都是从 from 开始的
-- 执行过程中，每个步骤都会为下个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入

------------------------------------------------------------------- create table

create table if not exists products
(
    id INT NOT NULL PRIMARY KEY,
    prod_id char(10) NOT NULL AUTO_INCREMENT,
    vend_id char(10) NOT NULL,
    prod_name char(254) NOT NULL,
    prod_price decimal(8,2),
    quantity integer NOT NULL DEFAULT 1,
    prod_desc text(1000) NULL,
）ENGINE=innodb,CHARSET=utf8,COMMENT 'emmmm...' ;

------------------------------------------------------------------- 复制表

-- 从已经有的表中复制表 ( 后接select )
create table TABLE2 as select * from TABLE1 where 1<>1;
-- 复制表（使用这种方式时新表将缺少key信息、自增列属性 auto_increment、索引）
-- 严格来讲select方法不能理解成复制表结构的方法，它只是执行了查询，所以复制的结果只包含了查询的字段和数据
-- 其它表属性都由系统配置文件决定；包括存储引擎、默认字符集等都由系统默认配置所决定

-- 复制表（使用like方式没有任何数据被克隆到新表，但除没有数据之外，结构被进行了完整克隆）
-- like 是专门复制表结构的方法，但是它只复制表的结构和相关属性而没有数据
create table ACTOR_LIKE like ACTOR;
-- 如果不需要考虑表原本的属性包括存储引擎、备注、主键、索引等那么select方法也可以，并且它还能连同数据一起复制

-------------------------------------------------------------------

-- SELECT ... INTO ... FROM ... ( 要求目标表TABLE2不存在，因为在插入时会自动创建表TABLE2 )
select v1,v2 [into TABLE2] from TABLE1

-------------------------------------------------------------------

-- 修改列：change、modify、alter、add、drop、rename、...
alter table TABLE_NAME change id sid int unsigned;          -- 修改id列名字为sid并把属性修改为int unsigned
alter table TABLE_NAME modify id int unsigned;              -- 修改id列类型为int unsigned
alter table TABLE_NAME alter i set default 1000;			-- 修改字段默认值
alter table TABLE_NAME alter i drop default;				-- 删除字段的默认值	
alter table TABLE_NAME add column 列名 varchar (20);         -- 新增列
alter table TABLE_NAME add column 列名 int after picid;      -- 新增列
alter table TABLE_NAME add primary key (picid)              -- 向表中增加一个主键
alter table TABLE_NAME drop primary key;                    -- 删除主键
alter table TABLE_NAME drop i;                              -- 删除指定字段
alter table TABLE_NAME character set gb2312                 -- 改变表的编码						
alter table TABLE_NAME type = myisam;                       -- 修改存储引擎
alter table TABLE_NAME rename tb_test02;                    -- 把表名改为 tb_test02

alter table TABLE_NAME add id_02 int(10) first              -- 增加字段 id_02 并且放在第一个字段
alter table TABLE_NAME add id_03 int(10) after id           -- 增加字段 id_03 并且放在 id 后面

-- 增加外键约束
alter table orderitems add constraint fk_orderitems_orders \
foreign key (order_num) references orders (order_num);

------------------------------------------------------------------- concat()

-- 联合字符或者多个列 (将列id与""和列name和"="连接为一个字符串)
select concat(id,'：',name,'=') from students;

-------------------------------------------------------------------

-- SHELL脚本调用SQL
mysql -u root -p${PASSWORD} <<'EOF'
    sql...;
    exit;
EOF

-- 以root登录后切换到mysql数据库创建用户并刷新权限
mysql -u root -p -hlocalhost --port 3306
mysql> use mysql;
mysql> insert into user (host,user,password) values ('%','username',password('password')) ; 
mysql> flush privileges;

-- 用文本方式将数据文件装入数据库表中
load data infile "d:/mysql.txt" into table TABLE_NAME;

-- 导入.sql文件命令
use DB_NAME; -- 选择要操作的数据库，使用该命令后所有Mysql命令都只针对该数据库
source d:/mysql.sql;

-- insert into 后可接 values形式、select形式，只需与左侧列数量和类型一致即可
-- 向 char、varchar、text、日期型字段插入时字段值要用单引号括起来
-- 向自增型 auto_increment 字段插入数据时建议插NULL，此时字段将向自增型字段插入下个编号 (其实平时都直接不写)
-- 插入新纪录时需注意表之间的外键约束，原则上先给父表插入数据后再给子表插入数据
insert into students (stuName,stuAge) values ('zhanghua',null),('zhanghua',14),('zhanghua',15);
insert into students (stuName,stuAge) select studentName_B,studentAge_B from student_info_B ;

-- 更新
update table set sex="F" where name='HYQ';

-- 删除（除非确实删除所有行或更新所有行，否则必须使用where子句进行限定）
-- 在update和delete语句使用where子句前应先用select进行测试，保证过滤条件是正确无误的
-- 强调一句，删除数据与修改数据，不加条件都是臭流氓
delete from customers where cust_id = '10000006'

select * from tutorials_tbl order by tutorial_author [asc|desc]         -- 排序
select column_name as alias_name from table_name;                       -- 列别名
select column_name from table_name as alias_name;                       -- 表别名

------------------------------------------------------------------- Select ...

-- 基本上项目中90%以上关于数据库的操作是查询，因此查询语句写的好不好将直接体现出编程能力

-- IN 操作符允许在 WHERE 子句中规定多个值
select column_name(s) from table_name where column_name in (value1,value2,...)

-- 操作符 between ... and 会选取介于两个值之间的数据范围，这些值可以是数值、文本或日期
select column_name(s) from table_name where column_name [not] between value1 and value2

-- 可以为列名称和表名称指定别名（Alias）
select column_name(s) from table_name as alias_table_name
select column_name as alias_column_name from table_name
select lastname as family, firstname as name from persons

-- 检索不同的值
select distinct vend_id from products;

-- 返 6,7,8,9,10 这5行数据
select prod_name from products limit 5 offset 5;

-- Order by 子句必须是select的最后一条语句 (ASC 升序(默认)，DESC 降序)
select prodname,prodprice from products order by prod_price　desc;

select prodname,prodprice,prodid from products where prodprice between 4 and 10
select * from customers where cust_email is NULL;       -- 空判断过滤数据
select * from customers where cust_email is NOT NULL;   -- 非空判断过滤数据

-- AND操作符优先级高于OR操作符
select prod_name, prod_price, vend_id from products \
    where vend_id = '1001' OR vend_id = '1005' and prod_price >= 10;

-- IN操作符比一组OR操作符更快
-- IN最大优点是可以包含其他SELECT，因此能动态建立WHERE子句，即: 查询将返回结果填充到括号 ...
select prodname,prodprice,vendid from products where vendid in ('1001','1003') order by prod_price desc;
select 学号,姓名 from student where 学号 in (select 学号 from student where 成绩 < 60 );

-- NOT 操作符否定其后所跟的任何条件
select * from products where NOT vendid = 1001 and prodname = 'safe';

-- 下划线 _ 只匹配单个字符
select prodid,prodname from products where prod_name like 'JetPack_000';

-- As 重新起个新名字
select concat(prod_name,'(', vend_id,')') as price_info from products;

-- 算术运算符：加，减，乘，除
select prod_id, quantity * item_price as expanded_price from orderitems where order_num = 20005;

-- 正则匹配
select name from student where name regexp '^wang|^li'

select count(*) from teacher where 教师姓名 like '孟%';
select sum(成绩) from score where 课程号 = '0002';
select count(distinct 学号) as 学生人数 from score;
select 课程号,max(成绩) as 最高分,min(成绩) as 最低分 from score group by 课程号;
select 课程号,count(学号) from score group by 课程号;
select 性别,count(*) from student group by 性别;
select 学号,avg(成绩) from score group by 学号 having avg(成绩) > 60;
select 学号,sum(成绩) from score group by 学号 order by sum(成绩);

-- 查询将返回结果填充到括号 ...
select 学号,姓名 from student where 学号 in (select 学号 from student where 成绩 < 60 );

select a.学号,a.姓名, avg(b.成绩) as 平均成绩 from student as a left join score as b \
    on a.学号 = b.学号 group by a.学号 having avg(b.成绩) > 85;

select a.学号,a.姓名,c.课程号,c.课程名称 \
    from student a \
    inner join score b on a.学号=b.学号 \
    inner join course c on b.课程号=c.课程号;

select * from products where (price between 10 and 20) and NOT categoryid in (1,2,3,'paris');	
select lastname,firstname,address from persons where address IS NOT NULL

select orders.orderid,customers.customername \
    from customers,orders where customers.customername='alfreds futterkiste';

select distinct productname, price from products where price > (select avg(price) from products);
select count(customerid) as ordersfromcustomerid from orders where customerid=7;
select avg(price) as priceaverage from products;
select len(column_name) from table_name;				--- 返回文本字段长度

------------------------------------------------------------------- 子查询

-- 在一个表达式中调用另一个表达式，这个被调用的表达式叫做子查询（subquery）

-- 表子查询实例 ( 将子查询的输出作为一个表再次进行select语句的处理 )
select playerno from (select playerno,sex from players where playerno < 10)

-- 行子查询实例 ( 将子查询的结果作为where查询的条件 )
select playerno from players where (sex,town) = (select sex,town from players where playerno = 100);

-- 标量子查询实例 ( 将子查询的结果作为where查询的条件，此处使用了函数进行列的处理后进行条件判断 )
select playerno from players where year(birth) = \
    (select year(birth) from players where playerno = 27) and playerno <> 27;

-- 列子查询实例( 将子查询的结果作为where查询的 IN 条件判断 )
select playerno,name from players where playerno in (select playerno from players where sex = 'F');

------------------------------------------------------------------- join

-- join 用于根据多个表中的列之间的关系，从这些表中查询数据
-- 有时为了得到完整结果，需要从两个或更多的表中获取结果就需要join。数据库中的表可通过相同的键值将彼此关联

-- 不使用join
select persons.lastname, persons.firstname, orders.orderno
from persons,orders
where persons.id_p = orders.id_p 
 
-- 使用join
select persons.lastname, persons.firstname, orders.orderno
from persons inner join orders
on persons.id_p = orders.id_p
order by persons.lastname

------------------------------------------

-- 表连接 (此处依据每行记录中相同的值，即返回两个表的交集)
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate \
FROM Orders INNER JOIN Customers \
ON Orders.CustomerID=Customers.CustomerID \
ORDER BY Customers.CustomerName;

INNER JOIN：	--- 若表中有至少一个匹配则返回行（若干表之间关联列的交集部分）
LEFT JOIN：		--- 从左表返回所有行，即使右表中没有匹配（右表未匹配到的显示为NULL）
RIGHT JOIN：	--- 从右表返回所有行，即使左表中没有匹配（左表未匹配到的显示为NULL）
FULL JOIN：		--- 只要其中一个表中存在匹配则返回行

-- Example
select * from student left join  course on student.ID=course.ID     -- 左连接
select * from student right join course on student.ID=course.ID     -- 右连接
select * from student inner join course on student.ID=course.ID     -- 内连接

------------------------------------------------------------------- 自连接

-- 查询成绩表中语文成绩大于数学的学生的信息 (自连接)
select * from 成绩表 as a,成绩表 as b \
where a.学生姓名=b.学生姓名 and a.课程名='语文' and b.课程名='数学' and a.成绩 > b.成绩

------------------------------------------------------------------- 使用子查询进行多表连接

select a.学号 ​​from (select 学号,成绩 from score where 课程号=01) as a \
inner join (select 学号,成绩 from score where 课程号=02) as b on a.学号 = b.学号 \
inner join student c on c.学号 =a.学号 \
where a.成绩 > b.成绩 ;

------------------------------------------------------------------- 约束

NOT NULL  		    -- 指示某列不能存储 NULL 值
UNIQUE  		    -- 保证某列每行必须唯一的值
PRIMARY KEY  	    -- NOT NULL 和 UNIQUE 的结合。确保某列(或多个列的结合)有唯一标识，使得快速找到表中特定记录
FOREIGN KEY  	    -- 用于本表中某列的数据匹配另一个表中特定列的值的参照完整性
CHECK  			    -- 保证列中的值符合指定条件
DEFAULT  		    -- 规定未给列赋值时的默认值

------------------------------------------

-- 创建
create table persons
(
    id_p int not null,
    p_id int not null AUTO_INCREMENT,
    name varchar(255) not null,
    city varchar(255) default 'sandnes', 
    constraint chk_person check (id_p>0 and city='sandnes')
)
-- 添加约束
alter table persons add constraint chk_person check (id_p>0 and city='sandnes')

-- 删除约束
alter table persons drop check chk_person

------------------------------------------------------------------- Index

-- 添加主键索引时需确保默认不为空
alter table TABLE_NAME modify i int NOT NULL;
alter table TABLE_NAME add primary key (i);
alter table TABLE_NAME drop primary key;

-- 应用于表创建完毕后添加的普通索引
alter table TABLE_NAME add index INDEX_NAME (column_list) ;
-- 应用于表创建完毕后添加的唯一索引
alter table TABLE_NAME add unique (column_list) ;
-- 应用于表创建完毕后添加的主键索引
alter table TABLE_NAME add primary key (column_list) ;
-- 在column_list中的各列之间用逗号分隔，索引名INDEX_NAME可选，缺省时MySQL将根据第一个索引列赋一个名称

-- CREATE INDEX 可用于对表增加普通索引或UNIQUE索引，可用于建表时创建索引
-- 但不能用 CREATE INDEX 语句创建PRIMARY KEY索引
create index index_name on TABLE_NAME (username(length)); 
create index index_name on TABLE_NAME (column_list)
create unique index index_name on TABLE_NAME (column_list)

-- 删除索引：
alter table TABLE_NAME drop index INDEX_NAME ;
alter table TABLE_NAME drop primary key ;

-------------------------------------------------------------------  show create table TABLE_NAME

-- 获取数据表的完整结构  ( 拷贝表指定数据使用INSERT INTO... SELECT...实现 )	
mysql> SHOW CREATE TABLE tutorials \G;					
       Table： tutorials
Create Table： CREATE TABLE `tutorials` (
  `tutorial_id` int(11) NOT NULL auto_increment,
  `tutorial_title` varchar(100) NOT NULL default '',
  `tutorial_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`tutorial_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) TYPE=MyISAM 		        -- 创建表时指定的其数据引擎

-- 若要设置表中first_name与last_name字段的数据不能重复，可设置双主键模式来设置数据唯一性
-- 若设置了双主键那么那个键的默认值不能为NULL
CREATE TABLE person_tbl
(
    sex CHAR(10),
    first_name CHAR(20) NOT NULL,			-- 非空
    last_name CHAR(20) NOT NULL,		    -- 非空
    PRIMARY KEY (last_name,first_name)		-- 双主键	
);type=innodb

-- mysql 使用 auto_increment 关键字来执行 auto-increment 任务
-- 默认 auto_increment 的开始值是 1，每条新记录递增 1
-- 要让 auto_increment 序列以其他的值起始，请使用下列 sql 语法
alter table persons auto_increment=100

-------------------------------------------------------------------

SUM() 		    -- 总和
MAX() 		    -- 最大值
MIN() 		    -- 最小值
AVG() 		    -- 平均值
COUNT()		    -- 行数
FIRST()		    -- 第一个记录的值
LAST() 		    -- 最后一个记录的值
UCASE() 		-- 转为大写
LCASE() 		-- 转为小写
MID()		    -- 从某个文本字段提取字符
LEN()   		-- 返回某个文本字段的长度
ROUND()		    -- 进行指定小数位的四舍五入
NOW()   		-- 返回当前的系统日期和时间
FORMAT()	    -- 格式化某个字段的显示方式

SELECT column_name FROM TABLE_NAME ORDER BY column_name ASC LIMIT 1;    	-- 第一行
SELECT column_name FROM TABLE_NAME ORDER BY column_name DESC LIMIT 1;   	-- 最后一行

-- 示例
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName DESC;

-- 下列含义相同，USING 用于连接表之间相关的列，仅在表之间用于连接的列名相同的情况下使用
SELECT * FROM book JOIN borrow USING (book_id); 
SELECT * FROM book JOIN borrow WHERE book.book_id = borrow.book_id;

-- 下列含义相同，左侧表每个列与右侧表所有记录进行连接，如左侧为小中大3种型号，右侧为商品表，需生成所有商品各类型号的情况
select * from T_student cross join T_class
select * from T_student, T_class

------------------------------------------------------------------- Group By

-- Group By 关键字必须出现在 WHERE 之后、Order by 之前，从字面理解就是根据 By 指定的列对数据进行分组
-- 所谓分组就是将一个“数据集”划分成若干小区域，然后针对这些小区域进行数据处理
-- Group By 子句可包含任意数目的列，因而可以对分组进行嵌套，更细致的进行分组
-- Group By 子句嵌套了分组，数据将在最后指定的分组上进行汇总！
-- Group By 子句中列出的每一列必须是检索列或有效表达式。如果在 select 中使用表达式则必须在group by子句中指定相同的表达式
-- 大多数SQL实现不允许 Group By 列带有长度可变的数据类型
-- 除聚集计算语句外，select 语句中的每一列都必须在 Group By 子句中给出

-- Where 子句和 Having 子句的区别：
--     1. Where子句过滤的是行，Having子句过滤的是分组
--     2. Having子句可以替代Where子句，但不建议这样做

select 类别,sum(数量) as 数量之和 from A [where ...] group by 类别
select 类别,sum(数量) as 数量之和 from A [where ...] group by 类别 order by sum(数量) desc;

select 类别,count(*) as 记录数 from A group by 类别;
select 类别,avg(数量) as 平均值 from A group by 类别;

select dept,max(salary) as maximum,min(salary) as minimum from staff \
group by dept having count(*) > 2 order by dept；

------------------------------- 聚合函数
    SUM() 	-- 总和
    MAX() 	-- 最大值
    MIN() 	-- 最小值
    AVG() 	-- 平均值
    COUNT()	-- 行数
    FIRST()	-- 第一个记录的值
    LAST() 	-- 最后一个记录的值

-- where 子句对查询结果进行分组前将不符合条件的行去掉（即在分组之前过滤数据）
-- where 条件中不能包含聚合函数（使用where条件过滤出特定的行）
-- having 子句的作用是筛选满足条件的组（即在分组之后过滤数据）
-- 条件中经常包含聚组函数，使用having条件过滤出特定的组，也可以使用多个分组标准进行分组
select 类别, sum(数量)from a where 数量 > 8 group by 类别 having sum(数量) > 10;

-- 首先where语句先选出价格大于4的商品，然后按vend_id进行分组，最后再对分组进行过滤
select vendid,count(*) as numprods \
    from products \
    where prodprice >= 4 \
    group by vendid \
    having count(*) > 2;

-------------------------------------------------------------------

-- 假如要列出订购物品GRANO1的所有顾客应怎样检索?
--      1. 从订单详情表(OrderItems)中查询订购物品GRANO1的所有订单编号
--      2. 根据订单编号，从表(Orders)中查询顾客ID
--      3. 根据顾客ID，从表(Customers)查询顾客信息
select cust_name, cust_contact from customers
    where cust_id IN (select cust_id from Order
    where order_num IN (select order_num from OrderItems  where prod_id = 'GRANO1'));

-- 假如要显示Customers表中每个顾客的订单总数应怎样写？
--      1. 从Customers表中检索顾客列表
--      2. 对于检索的每个顾客，统计在Orders表中的数目
select cust_name, cust_state, (select count(*) from Orders where Orders.cust_id = Customers.cust_id) AS orders \
from Customers order by cust_name;

------------------------------------------------------------------- union

-- SQL允许执行多条查询语句并将结果作为一个查询结果集返回 (利用UNION操作符将多条select语句合成一个结果集)
-- 主要有两种情况需要使用组合查询：
--      1. 在一个查询中从不同的表返回结构数据
--      2. 对一个表执行多个查询，按一个查询返回数据
--      注：多个Where子句的Select语句都可以作为一个组合查询，也就是说将Where子句拆分开来

-- 该例中列相同，表相同，仅查询条件不同 ...
-- union 内部的 select 语句必须拥有相同数量的列。列也必须拥有相似数据类型。同时每条 select 语句中的列的顺序必须相同
select cust_name, cust_contact, cust_email from Customers
where cust_state in ('Illinois','Indiana','Michigan')
UNION
select cust_name, cust_contact, cust_email from Customers
where cust_name = 'Fun4All';

-- 上述语句相当于如下一条语句
select cust_name, cust_contact, cust_email from Customers
where cust_state in ('Illinois','Indiana','Michigan') or cust_name = 'Fun4All'

-------------------------------------------------------------------

-- 从"Customers"表的"City"列提取前4个字符  
SELECT MID(City,1,4) AS ShortCity FROM Customers

-- LEN() 函数返回文本字段中值的长度			
SELECT CustomerName,LEN(Address) as LengthOfAddress FROM Customers;

-- NOW() 函数返回当前系统的日期和时间
SELECT ProductName, Price, Now() AS PerDate FROM Products;

-- FORMAT() 函数用于对字段的显示进行格式化	
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate FROM Products;

-- Auto-increment
-- 会在新记录插入表中时生成唯一的数字。通常希望在每次插入新记录时自动地创建主键字段的值。可在表中创建auto-increment字段
CREATE TABLE Persons
(
    ID int NOT NULL AUTO_INCREMENT,			--- 自动增加的唯一数字，默认AUTO_INCREMENT开始值是1
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255),
	PRIMARY KEY (ID)
)TYPE=innodb;

-- 删除指定表的默认值约束
ALTER TABLE Student DROP constraint XXXX

-- JSON查询子key特定值
select * from user where data->'$.username' = 'admin';

-------------------------------------------------------------------　外键

-- MySQL创建关联表可理解为是两个表之间有个外键关系，但这两个表必须满足三个条件
--  1.两个表必须是InnoDB存储引擎
--  2.使用在外键关系的域必须为索引型 (Index)
--  3.使用在外键关系的域必须数据类型相似

-- 外键的作用：
-- 主要目的是控制存储在外键表中的数据，使两张表形成关联，外键只能引用外表中的列的值！
-- 相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
-- 外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性
-- 主表删除某条记录时，从表中与之对应的记录也必须有相应改变
-- 一个表可以有多个外键，外键可以为空值，若不为空值则每一个外键的值必须等于主表中主键的某个值

-- 创建含有外键的表
create table temp (
    id int,
    name char(20),
    foreign key(id) references outTable(id) on delete cascade on update cascade
);  -- 把id列设为外键，参照外表outTable的id主键列，当外键的值删除时本表中对应的列删除，当外键值改变时本表中对应的列值改变
--     这是数据库外键定义的一个可选项，用来设置当主键表中的被参考列的数据发生变化时外键表中响应字段的变换规则 ...

-- 事件触发限制：on delete、on update 可设参数
--      cascade (跟随外键改动)
--      restrict (限制外表中的外键改动)
--      set Null (设空值）
--      set Default（设默认值）
--      [默认]no action

--------------------------------------- Example

create table s_orderform (
    o_id int auto_increment primary key,
    o_buyer_id int,
    o_seller_id int,
    o_totalprices double,
    o_state varchar(50),
    o_information varchar(200),
    foreign key(o_buyer_id) references s_user(u_id),      -- 外连到 s_user 表的 u_id 字段
    foreign key(o_seller_id) references s_user(u_id)      -- 外连到 s_user 表的 u_id 字段
)

--------------------------------------- Example

-- 父表
create table if not exists work (
  id bigint auto_increment comment '主键',
  name varchar(20) not null comment '工作名称',
  create_time date default '2021-04-02',
  primary key(id)
)

-- 子表中使用 foreign key 进行外键约束
create table if not exists per (
  id bigint auto_increment comment '主键',
  name varchar(20) not null comment '人员姓名',
  work_id bigint not null comment '工作id',
  create_time date default '2021-04-02',
  primary key(id),
  foreign key(work_id) references work(id)
) -- 从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致

-- 删除外键约束
-- ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

------------------------------------------------------------------- 临时表

-- 当数据库存在大量数据时想要获取到这个数据集合的一个子集，就可以使用临时表来保存我们想要的数据
-- 然后对临时表进行操作就可以了...使用临时表必然是有原因的，它会加快数据库的查询性能...
-- 并不是使用了临时表查询性能一定就会提升，当数据使用了很好的索引时临时表的速度可能并不快...
-- 临时表在我们与数据库断开连接时Mysql会自动删除临时表并释放其占用空间..除了这种方式还可以手动删除...
-- 临时表的创建很简单，只需在建表时加关键字 "temporary" 即可...
create <temporary> table tmp_table  -- 新建临时表
(
    name varchar(10) not null,
    value integer not null,
);

-- 将查询的数据插入到临时表中...
create temporary table tmp_table select *　from table_name;

-- 使用alter来重命名临时表...
alter table tmp_table rename ttmp_table;

-- 与我们正常删除表的语句一样...
drop table tmp_table

-- 临时表的使用也是有以下限制的:
--      临时表只能使用在 memory,myisam,merge,innodb 存储引擎下 ...
--      临时表不支持mysql簇 ...
--      在同一个query语句中，我们只能查询一次临时表 ...
--      show tables语句不会列举临时表信息 ...
--      不能使用rename来重命名临时表，但可以使用 alter table 来代替

-------------------------------------------------------------------　视图

-- 视图是虚拟表，也称派生表，其内容都是派生自其它表的查询结果
-- 虽然视图看起来感觉和基本表一样，但不是基本表。基本表的内容是持久的，而视图的内容是动态的
-- 视图可以嵌套，即: 可利用从其他视图中检索数据的查询来构造视图，但允许的嵌套层数在不同的DBMS中有所不同
-- 许多DBMS禁止在视图查询中使用 ORDER BY 子句进行排序
-- 有些DBMS要求对返回的所有列进行命名，如果列是计算字段则需要使用别名
-- 视图不能索引，也不能有关联的触发器或默认值
-- 用户在使用时必须对视图有足够的访问权限

-- 使用视图的优点：
--      1. 可靠的安全性
--      2. 查询性能提高
--      3. 有效应对灵活性的功能需求
--      4. 轻松应对复杂的查询需求

-- 创建视图 ( 创建视图后可通过查看数据库中的全部数据表来查看 )
create view <NAME> as select .... from ....;

-- 对视图进行更新操作 (视图可以更新!)
update STU_VIEW set age=1000 where id=1;

-- 使用右侧查询的条件作为update中条件查询中id的匹配值，即: 右侧查询将返回结果填充到括号 ...
update STU_VIEW set age=1000 where id = (select id from ... where ...=...)

-- 删除视图
drop view STU_VIEW;

-- 视图是可更新的，这意味着可使用 insert 或 update 语句通过更新视图插入或更新基表的行
-- Tips：有些DBMS仅把视图作为只读的查询。在Oracle中对于单一表查询创建视图后可进行update；对于多表查询创建视图则不能直接update

-------------------------------------------------------------------

-- 关闭所有打开的表，同时对所有数据库中的表都加读锁，直到显式执行 unlock tables
-- 该操作常用于数据备份（此时直接拷贝数据文件也是安全的）...
flush tables with read lock
-- 在flush tables with read lock成功获得锁之前必须等待所有语句执行完成（包括SELECT）
-- 所以如果有慢查询在执行，或一个打开的事务，或其他进程拿着表锁时就会被阻塞，直到所有的锁被释放 ...

-- explain用来分析Mysql查询结构，主要关注四个参数值：type、key、rows、extras
-- 访问类型type：all最差、ref、eq_ref居中、null最好
-- all -> index -> range -> ref -> eq_ref -> const 或 system -> null
-- 有无使用索引 key (key为空没有使用索引)
-- 找到所需记录要读取的行数：rows，这个值越小越好
-- extras：在什么方式下找到了所需记录：
--    出现 using filesort 或 using temporary 表明效率低下
--    only index 用到了索引
--    where used 用到了where过滤条件
--    impossible where 没用到索引

-------------------------------------------------------------------

-- 查看表状态
robin@localhost[sakila]> show table status like 'actor'\G
-- *************************** 1. row ***************************
--            Name： actor
--          Engine： InnoDB
--         Version： 10
--      Row_format： Compact
--            Rows： 200
--  Avg_row_length： 81
--     Data_length： 16384
-- Max_data_length： 0
--    Index_length： 16384
--       Data_free： 0
--  Auto_increment： 201
--     Create_time： 2014-12-25 13：08：25
--     Update_time： NULL
--      Check_time： NULL
--       Collation： utf8_general_ci
--        Checksum： NULL
--  Create_options： 
--         Comment： 

-- 查看表索引
robin@localhost[sakila]> show index from 'actor'\G
-- *************************** 1. row ***************************
--         Table： actor
--    Non_unique： 0
--      Key_name： PRIMARY
--  Seq_in_index： 1
--   Column_name： actor_id
--     Collation： A
--   Cardinality： 200
--      Sub_part： NULL
--        Packed： NULL
--          Null： 
--    Index_type： BTREE
--       Comment： 
-- Index_comment： 
-- *************************** 2. row ***************************
--         Table： actor
--    Non_unique： 1
--      Key_name： idx_actor_last_name
--  Seq_in_index： 1
--   Column_name： last_name
--     Collation： A
--   Cardinality： 200
--      Sub_part： NULL
--        Packed： NULL
--          Null： 
--    Index_type： BTREE
--       Comment： 
-- Index_comment： 

-- 查看表结构
robin@localhost[sakila]> desc actor;
-- +-------------+----------------------+------+-----+-------------------+-----------------------------+
-- | Field       | Type                 | Null | Key | Default           | Extra                       |
-- +-------------+----------------------+------+-----+-------------------+-----------------------------+
-- | actor_id    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
-- | first_name  | varchar(45)          | NO   |     | NULL              |                             |
-- | last_name   | varchar(45)          | NO   | MUL | NULL              |                             |
-- | last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
-- +-------------+----------------------+------+-----+-------------------+-----------------------------+
-- 4 rows in set (0.00 sec)

------------------------------------------------------------------- select into

-- select into 语句从一个表中选取数据，然后把数据插入另一个表中
-- select into 语句常用于创建表的备份复件或者用于对记录进行存档

-- 把select的mytable表中的数据导出到/home/db_bak2012文件
select * from mytable where status != 0 into outfile '/home/db_bak2012' \
fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;

-- 导入刚才备份的数据，可以使用 load data .... into table ...
load data infile '/home/db_bak2012' into table mytable_bak \
fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;

-- SELECT ... INTO ... FROM ... ( 要求目标表TABLE2不存在，因为在插入时会自动创建表TABLE2 )
select v1,v2 [into TABLE2] from TABLE1

------------------------------------------------------------------- 存储过程

-- 存储过程类似于 C 中的方法，它有三个优点：简单、安全、高性能
-- 存储过程是为以后使用而保存的SQL语句和一些特殊的控制结构，可将其视为批文件，但其作用不仅限于批处理
-- 当希望在不同的应用或平台上执行相同的函数，或封装特定功能时存储过程时非常有用
-- 多数 DBMS 的存储过程可能不同，而且存储过程也比较复杂，难编写
-- 存储过程经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用
-- 因为它是预编译的，在首次运行存储过程时查询优化器对其进行分析优化，并给出最终被存储在系统表中的执行计划
-- 存储过程根据需要可能会有输入、输出、输入输出参数

drop procedure if exists `proc1`;

delimiter //        -- DELIMITER 是分割符，因为MySQL默认以";"为分隔符
                    -- 如果没有声明分割符则编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错
    create procedure proc1(out s int)  
    begin           -- 过程体的开始与结束使用BEGIN与END标识（这里将select的结果使用Into的形式写到变量中，该变量的参数的类型是out，变量类型是int...）

        select count(*) into s from user;
        -- 这里有一个输出参数 s，类型是int型，如果有多个参数用","分割开，这里使用 into 关键字将其输出
    end 

//
delimiter ; 


-- 存储过程的参数用在存储过程的定义，共有三种参数的类型: IN、OUT、INOUT
-- 形式如：create procedure([[in|out|inout] 参数名 数据类形...])
-- IN 输入参数: 表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
-- OUT 输出参数: 该值可在存储过程内部被改变，并可返回
-- INOUT 输入输出参数: 调用时指定，并且可被改变和返回

-- IN 参数例子
mysql > DELIMITER //  
mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)  
-> BEGIN  
->      SELECT p_in;  
->      SET p_in=2;  
->      SELECT p_in;  
->      -- p_in 虽然在存储过程中被修改，但并不影响 @p_id (会话级别) 的值
-> END;
-> //  
mysql > DELIMITER ;


-- OUT 参数例子
mysql > DELIMITER //  
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)  
-> BEGIN 
->      SELECT p_out;  
->      SET p_out=2;  
->      SELECT p_out;
-> END;  
-> //  
mysql > DELIMITER ;


--  INOUT 参数例子
mysql > DELIMITER //   
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)   
-> BEGIN 
->  SELECT p_inout;  
->  SET p_inout=2;  
->  SELECT p_inout;  
-> END;  
-> //  
mysql > DELIMITER ;
-- 调用存储过程
mysql > SET @p_inout=1;  
mysql > CALL demo_inout_parameter(@p_inout) ;  
+---------+  
| p_inout |  
+---------+  
|    1    |  
+---------+  
 
+---------+  
| p_inout |  
+---------+  
|    2    |  
+---------+  
 
mysql > SELECT @p_inout;  
+----------+  
| @p_inout |  
+----------+  
|    2     |  
+----------+ 

-- 变量定义
-- DECLARE variable_name[,variable_name...] datatype [DEFAULT value];
-- 其中 datatype 为MySQL的数据类型，如 int,float,date,varchar(length)
-- 例如
-- DECLARE l_int int unsigned DEFAULT 4000000;  
-- DECLARE l_numeric number(8,2) DEFAULT 9.95;  
-- DECLARE l_date date DEFAULT '1999-12-31';  
-- DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
-- DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';  

-- 变量赋值
-- SET 变量名 = 表达式值 [,variable_name = expression ...]

-- 在存储过程中使用用户变量
mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
mysql > SET @greeting='Hello';  
mysql > CALL GreetWorld( );  
+----------------------------+  
| CONCAT(@greeting,' World') |  
+----------------------------+  
|  Hello World               |  
+----------------------------+  

-- if-then -else 语句
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     ->     declare var int;  
     ->     set var=parameter+1;  
     ->     if var=0 then 
     ->         insert into t values(17);  
     ->     end if;
     -> 
     ->     if parameter=0 then 
     ->         update t set s1=s1+1;  
     ->     else 
     ->         update t set s1=s1+2;  
     ->     end if;  
     -> end;  
     -> //
mysql > DELIMITER ; 

-- case 语句
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3 (in parameter int)  
     -> begin 
     ->     declare var int;  
     ->     set var=parameter+1;  
     ->     case var  
     ->         when 0 then  
     ->             insert into t values(17);  
     ->         when 1 then  
     ->             insert into t values(18);  
     ->         else  
     ->             insert into t values(19);  
     ->     end case;  
     -> end;  
     -> //  
mysql > DELIMITER ; 


-- while ···· end while
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     ->     declare var int;  
     ->     set var=0;  
     ->     while var < 6 do  
     ->         insert into t values(var);  
     ->         set var=var+1;  
     ->     end while;
     -> end;
     -> //  
mysql > DELIMITER 

------------------------------------------------------------------- Example

-- 修改结束符为$$, 以免程序把存储过程定义的 begin 与 end  之间的 ; 作为结束符 
delimiter $$

drop procedure if exitsts proc_p1 $$    -- 如果存在 proc_p1 就先删除

create procedure proc_p1( in i1 int )   -- 需要一个 int 类型的参数，该参数作为输入使用  
begin                                   -- 存储过程的代码放在 begin 与 end 之间  

    declare d1 int;                     -- 声明一个d1变量  
    declare d2 int default 3;           -- 声明一个默认值为3的d2变量
    set d1 = i1 + i2;  
    select * from man_to_women where nid > d1;  

end $$

-- 修改结束附为默认的分号，以免影响其他语句  
deliniter ;

-- 调用存储过程  
-- call proc_p1(2) 

------------------------------------------------------------------- 临时表 + 存储过程

create table user_info
(
   user_id int not null,
   user_name varchar(50) not null
);

-- 假设插入了10000条数据信息...那么想要查询 id>5000 and id<8000 的数据信息，那么就可以使用一个临时表了...
insert into user_info values(1,'aa'),(2,'bb').......

drop procedure if exists query_performance_test;

delimiter $$

create procedure query_performance_test()
begin
    declare begintime;
    declare endtime;
    set begintime=curtime();
    drop temporary if exists userinfo_tmp;
    create temporary table userinfo_tmp
    (
         i_userid int not null,
         v_username varchar(50) not null
    ) engine=memory;
    insert into userinfo_tmp(i_userid,v_username) select user_id,user_name from userinfo where   userid>5000 and userid<8000;  -- 将想要查询的数据放置到临时表中...
    select * from userinfo_tmp;
    set endtime=curtime();
    select endtime-begintime;
end $$

delimiter; -- 将结束符号重新定义为默认的分号 ...

call query_profromance_test(); -- 调用存储过程 ...

------------------------------------------------------------------- 数据库安全

-- 一般对数据保护操作有：
--     1. 对数据库管理功能 (创建表、更改或删除已存在的表等) 的访问
--     2. 对特定数据库或表的访问
--     3. 访问的类型 (只读、对特定列的访问等)
--     4. 仅通过视图或存储过程对表进行访问
--     5. 创建多层次的安全措施，从而允许多种基于登录的访问和控制
--     6. 限制管理用户账号的能力

------------------------------------------------------------------- case when

-- CASE WHEN 条件表达式函数类似JAVA中的 IF ELSE 语句：
-- 格式:
--      CASE WHEN condition THEN result
--          [WHEN...THEN...]
--          ELSE result
--      END

-- condition 是返回布尔类型的表达式，如果表达式返回true则整个函数返回相应result的值
-- 如果表达式皆为false则返回else后result的值，如果省略了ELSE子句则返回NULL
-- 如：CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END

--------------------------------------

-- Case 函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略
-- Case 具有两种格式。简单Case函数和Case搜索函数
--简单Case函数
    CASE sex
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
    ELSE '其他' END

--Case搜索函数
    CASE 
    WHEN sex = '1' THEN '男'
    WHEN sex = '2' THEN '女'
    ELSE '其他' END

-------------------------------------- Example

SELECT 
	SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
	SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM 
	THTF_STUDENTS

-- 输出:
-- MALE_COUNT	FEMALE_COUNT	MALE_PASS	FEMALE_PASS
-- 3	        3	            1	        3

--------------------------------------

SELECT
    STUDENT_NAME,
    (
        CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM
    TABLE

--------------------------------------

SELECT 
	E_CODE,
	SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,  --水耗
	SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,    --电耗
	SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY    --热耗
FROM 
	THTF_ENERGY_TEST
GROUP BY
	E_CODE

--------------------------------------

SELECT SUM(population),
    CASE country
        WHEN '中国' THEN '亚洲'
        WHEN '印度' THEN '亚洲'
        WHEN '日本' THEN '亚洲'
        WHEN '美国' THEN '北美洲'
        WHEN '加拿大'  THEN '北美洲'
        WHEN '墨西哥'  THEN '北美洲'
        ELSE '其他' END     --  THEN后边的值与ELSE后边的值类型应一致，否则会报错
FROM Table_A  GROUP BY
    CASE country
        WHEN '中国'  THEN '亚洲'
        WHEN '印度'  THEN '亚洲'
        WHEN '日本'  THEN '亚洲'
        WHEN '美国'  THEN '北美洲'
        WHEN '加拿大'  THEN '北美洲'
        WHEN '墨西哥'  THEN '北美洲'
        ELSE '其他' END;

--------------------------------------

-- 判断工资的等级并统计每个等级的人数
SELECT
    CASE WHEN salary <= 500 THEN '1'
         WHEN salary > 500 AND salary <= 600  THEN '2'
         WHEN salary > 600 AND salary <= 800  THEN '3'
         WHEN salary > 800 AND salary <= 1000 THEN '4'
         ELSE NULL END,
    salary_class, COUNT(*)
FROM  Table_A  GROUP BY \
    CASE WHEN salary <= 500 THEN '1'
         WHEN salary > 500 AND salary <= 600  THEN '2'
         WHEN salary > 600 AND salary <= 800  THEN '3'
         WHEN salary > 800 AND salary <= 1000 THEN '4'
         ELSE NULL END;

--------------------------------------

-- 使用 case when 对数据处理后使用该结果进行where的条件判断 ...
select * from test where stateid in (10,20) and ( case when stateid = 20 and userid <> 2 then 0 else 1 end ) = 1