mysql的常用语句
sql及其规范
sql是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。
在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持sql。
语法规则
-
在数据库系统中，SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。建议命令大写，表名库名小写。
-
SQL语句可单行或多行书写，以“;”结尾。关键词不能跨多行或简写。
-
用空格和缩进来提高语句的可读性。子句通常位于独立行，便于编辑，提高可读性。
-
注释：单行注释：-- ；多行注释：/*......*/。
-
sql语句可以折行操作。
-
常用命令
| 操作 | 命令 |
|---|---|
| 查看警告集 | show warnings; |
| 显示时间 | select now(); |
| 查看版本 | select version(); |
| 查看用户信息 | select user,host,password from mysql.user; |
数据库的操作（DDL）
为直观表达语句，用PH_加语义单词（PH_DBname，ph_dbname）作为可替换的占位符。
| 操作 | sql语句 |
|---|---|
| 显示所有数据库 | show databases; |
| 创建数据库 | create database PH_DBname; |
| create database if not exists PH_DBname; | |
| 查看库创建信息 | show create database PH_DBname; |
| 设置字符编码创建 | create database if not exists PH_DBname charater set gbk; |
| 修改数据库 | alter database PH_DBname character set utf8; |
| 删除数据库 | drop database if exists ph_dbname; |
| 使用或切换数据库 | use PH_DBname; |
| 查看当前使用的数据库 | select database(); |
select now(); /*显示当前时间*/ show databases; /* #显示所有数据库（文件夹）*/ create database 库名; /*#创建数据库（文件夹）*/ create database if not exists 库名; /*#创建数据库，如果存在则通过。*/ show warnings; /*#查看警告信息*/ show create database 库名; /*#查看创建库的信息*/ create database if not exists 库名 character set gbk; /*#创建或修改库的字符集为gbk*/ use 库名; /*#切换库类似cd的操作。*/ select database(); /*#查看正在使用的库，即当前目录*/
数据表的操作
为直观表达语句，用[...]括住的表示可选项操作。
完整性约束条件
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录（非空且唯一）
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
创建表
create table 表名; /*#创建数据表（文件）*/ create table table_name( field1 type[完整性约束条件], field2 type, ... fieldn type )[character set xxx]; CREATE TABLE test_table( id TINYINT PRIMARY KEY auto_increment, name VARCHAR(25), gender boolean, age INT default 18, /*设默认值*/ salary DOUBLE(7,2) /*最后一个字段后面不用加逗号*/ ); /*约束 primary key 非空唯一，能够唯一区分当前记录的字段称为主键 not null unique auto_increment 主键字段必须是数字类型 foreign key 外键约束*/
查看表结构
show tables; /*#查看当前数据库中的所有表名*/ desc 表名; /*#查看关于表的信息*/ show create table 表名; /*#查看创建表的信息*/ show columns from 表名; /*#查看表结构*/
修改表结构
--(1)增加列（字段） alter table 表名 add [column] 列名 类型 [完整性约束条件] [first|after 字段名]; alter table table_name add column_name varchar(20) not null unique first/after field_name; /*添加多个字段*/ alter table table_name add column1 varchar(20), add column2 int first, add column3 varchar(20) after column1; --(2)修改-列类型 alter table 表名 modify 列名 类型 [完整性约束条件] [first|after 字段名]; alter table table_name modify column_name tinyint default 20; --(3)修改列名 alter table 表名 change 列名 新列名 类型 [完整性约束条件] [first|after 字段名]; alter table table_name change name new_name int default 28 first; --(4)删除列 alter table 表名 drop 列名; alter table table_name drop column, add new_column; --(5)修改表名 rename table 表名 to 新表名; --(6)修改表所有字符集 alter table 表名 chararcter set utf8;
删除表
drop table 表名;
添加/删除主键
alter table 表名 add primary key(字段名称...); alter table 表名 drop primary key; eg: mysql> create table test5(num int auto_increment); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key create table test(num int primary key auto_increment); -- 思考，如何删除主键？ alter table test modify id int; -- auto_increment没了，但这样写主键依然存在，所以还要加上下面这句 alter table test drop primary key;-- 仅仅用这句也无法直接删除主键
唯一索引
alter table tab_name add unique [index|key] [索引名称](字段名称,...) alter table users add unique(name)-- 索引值默认为字段名show create table users; alter table users add unique key user_name(name);-- 索引值为user_name -- 添加联合索引 alter table users add unique index name_age(name,age);#show create table users; -- 删除唯一索引 alter table tab_name drop {index|key} index_name
完整性约束条件之主键约束
主键字段特点：非空且唯一
多字段联合主键
create table users2( id INT, name varchar(20), city varchar(20), primary key(name,id) );
<1> 一张表只能有一个主键
<2> 主键类型不一定非是整型
表记录（DCL）之增删改查
增加一条记录insert
insert into 表名 (field1,field2,.....) values (value1,value2,.....); insert into 表名 (field1,field2,.....) set field1=value1,field2+=field2,...; /*#键值对形式*/
增加多条记录insert
insert into 表名 (field1,field2,.....) values (value1,value2,.....), (value1,value2,.....), (value1,value2,.....); insert into 表名 values (value1,value2,.....), (value1,value2,.....), (value1,value2,.....);
修改表记录
update 表名 set fielda=value1,field2=value2,......[where 语句]; update 表名 set fielda=value1,field2=value2 where field3=xxx; /* UPDATE语法可以用新值更新原有表行中的各列。 SET子句指示要修改哪些列和要给予哪些值。 WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。*/
删除表记录
delete from 表名 [where ......]; delete from 表名 where field1=value1 or field1=value2; /*如果不跟where语句则删除整张表中的数据。 delete 只能用来删除一行记录。 delete 语句只能删除表中的内容，不能删除表的本身，想要删除表，用drop。 truncate table 也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在事务中恢复。*/ -- 删除表中名称为’alex’的记录。 delete from employee_new where name='alex'; -- 删除表中所有记录。 delete from employee_new;-- 注意auto_increment没有被重置:alter table employee auto_increment=1; -- 使用truncate删除表中记录。 truncate table emp_new;
查看表记录之单表查询
-- 查询表达式 SELECT *|field1,filed2 ... FROM tab_name WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数
--(1)普通查询 select [distinct] *|field1,field2,... from 表名; --查看表记录,其中from指定从哪张表筛选，*表示查找所有列，也可以指定一个列。distinct可去重查看。 --(2)select可以使用表达式，并且可以使用：字段 as 别名。 select [distinct] field1+30,field2-20,... from 表名; /*#显示表中某字段记录作运算后的效果。*/ select [distinct] field1+30 as 别名,field2-20 as 别名,... from 表名; /*#显示表中某字段记录作运算后的效果。*/ --(3)使用where子句，进行过滤查询。 select [distinct] *|field1,field2,... from 表名 where 表达式; /*#表达式支持比较运算，逻辑运算。*/ /* where字句中可以使用： between 80 and 100 #值在80到100之间 in(10,20,30) #值是10或20或30 like 'yuan%' #模糊匹配查询，数量符%表示任意多字符，数量符_表示一个字符，两个_则表示两个字符。 regexp 正则字符串 #正则表达式匹配*/ --(4)order by 指定排序的列：排序的列即可是表中的列名，也可以是select语句后指定的别名。 select [distinct] *|field1,field2,... from 表名 order by 字段 [Asc|Desc] /*#Asc(升序)为默认,Desc(降序),order by 子句应位于select语句的结尾。*/ --(5)group by 分组查询 /*注意：按分组条件分组后每一分组只会显示第一条记录 group by子句，其后可以接多个列名，也可以跟having子句，对group by的结果进行筛选。*/ select * from 表名 group by 2 /*#按位置字段筛选。*/ /* having和where两者都可以对查询结果进一步的过滤，差别有： （1）where语句只能用在分组之前筛选，having可以用在分组之后筛选。 （2）使用where语句的地方都可以用having进行替换。 （3）having中可以用聚合函数，where中就不行。#select field1,sum(field2) from 表名 group by field having sum(field2)>20;*/ --(6)聚合函数：先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可。（一般合分组查询配合使用） ifnull(字段,value) /*#如果字段里有null，则换成value。null和所有数计算都等于null。*/ aug(field) /*#求均值*/ sum(field) /*#求和*/ count(field) /*#计数*/ max(field) /*#最大值*/ min(field) /*#最小值*/ --(7)重点：select from where group by having order by 的执行顺序： /*mysql在执行sql语句时的执行顺序：from --> where --> select --> group by --> having --> order by。*/ select js as js成绩 from 表名 where js成绩 > 70; /*不成功*/ select js as js成绩 from 表名 having js成绩 > 70; /*成功*/ select * from 表名 as 成绩 where 成绩 > 70; /*成功*/ --(8)limit：限制显示记录的个数。 select * from 表名 limit 3; select * from 表名 limit 2,2; /*跳过2条，显示2条。*/ --(9)使用正则表达式查询。 select * from 表名 where 字段 regexp 正则表达式字符串;
外键约束
切记：作为外键的字段一定要和关联主键的数据类型保持一致。
创建外键：
--主表-- CREATE TABLE main( test INT ) --子表-- CREATE TABLE test( id INT primary key auto_increment, /*创建主键。*/ age INT foreign key (age) references main(test) /*创建外键作关联，table_name是主表的表名。*/ ) ENGINE=INNODB; #搜索引擎
增加外键和删除外键：
alter table test add constraint abc foreign key (age) references main(test); /*#abc为外键的名。*/ alter table test drop foreign key abc;
innodb支持的on语句：
--外键约束对子表的含义：如果在主表中找不到候选键，则不允许在子表上进行insert/update。 --外键约束对主表的含义：在主表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时，主表的行为取决于：在定义子表的外键时指定的on update/on delete子句。 ----------innodb支持的四种方式------------ --（1）cascade方式，在主表上update/delete记录时，同步update/delete掉子表的匹配记录。 /*外键的级联删除：如果主表中的记录被删除，则子表中对应的记录自动被删除。*/ foreign key (age) references main(test) on delete cascade --(2)set null方式：在主表上update/delete记录时，将子表匹配记录的列设为null，要注意子表的外键列不能为not null。 foreign key (age) references main(test) on delete set null --(3)restrict方式：拒绝对主表进行删除更新操作（了解） --(4)no action方式：在mysql中同restrict。（了解）
多表查询
连接查询：
内连接：inner join
外连接：left join；right join
全连接：full join
--(1)笛卡尔积查询 /*两张表中一条一条对应的记录，m条记录和n条记录查询，最后得到m*n条记录，其中很多错误的数据。*/ selete * from table1,table2; --(2)内连接 /*查询两张表中都有的关联数据，相当于利用条件从笛卡尔积结果中筛选出了正确的结果。*/ selete * from table1,table2 where table1.feild1=table2.field2; selete table1.field1,table2.field2 from table1,table2 where table1.feild1=table2.field2; selete * from table2 inner join table1 on table2.feild2=table1.field1; --(3)外连接 /*外连接：在内连接的基础上增加左边有右边没有的结果，以左表显示为主。*/ selete * from table2 left join table1 on table2.feild2=table1.field1; /*右外连接：在内连接的基础上增加右边有左边没有的结果，以右表显示为主。*/ selete * from table2 left join table2 on table1.feild2=table1.field1; selete * from table2 right join table1 on table2.feild2=table1.field1; --(4)全连接在内连接基础上增加左边有右边没有和右边有左边没有的结果 selete * from table2 full join table1 on table2.feild2=table1.field1; #mysql不支持全连接。 /*mysql可以使用以下方法实现全连接：*/ selete * from table2 left join table1 on table2.feild2=table1.field1 union selete * from table2 right join table1 on table2.feild2=table1.field1; selete * from table2 left join table1 on table2.feild2=table1.field1 union all selete * from table2 right join table1 on table2.feild2=table1.field1; /*注意union与union all的区别：union会去掉相同的记录。*/
多表查询之复合条件连接查询
多表查询之复合条件连接查询
--子查询是将一个查询语句嵌套在另一个查询语句中。
--内层查询语句的查询结果，可以为外层查询语句提供查询条件。
--子查询中可以包含：in、not in、any、all、exists 和 not exists等关键字
--还可以包含比较运算符。
select * from table1,table2 where table1.feild1=table2.field2 and 条件表达式; --(1)带in关键字的子查询 select * from table1 where field1 in (select field from table2); create table table_name(select field from table2); /*#复制表table2*/ --(2)带比较运算符的子查询 select * from table1 where field1 in (select distinct field_a from table2 where field_b > 30); #一般使用distinct去重后的结果 --(3)带exists关键字的子查询 /*exists关键字表示存在，在使用exists关键字是，内存查询语句不返回查询的记录，而是返回一个bool值。 当返回True时，外层语句将进行查询；当返回False时，外层查询语句不进行查询。*/ select * from table1 where exists (select distinct field_a from table2 where field_b=30);/* #一般使用distinct去重后的结果*/
索引
索引在MySQL中也叫做"键"，是存储引擎用于快速找到记录的一种数据结构。
--语法（中括号的内容可加可不加）： create table 表名( 字段名1 数据类型 [完整性约束条件...], 字段名2 数据类型 [完整性约束条件...], [unique | fulltext | spatial] index|key [索引名] (字段名[(长度)] [asc | desc])); --创建普通索引 index|key [索引名] (字段名[(长度)] [asc | desc]) --创建唯一索引 unique index|key [索引名] (字段名[(长度)] [asc | desc]) --创建文本索引 fulltext index|key [索引名] (字段名[(长度)] [asc | desc]) --创建多列索引 index|key [索引名] (字段名1,字段名2,字段名3,... [asc | desc]) --添加索引 create [unique | fulltext | spatial] index 索引名 on 表名 (字段名[(长度)] [asc | desc])); alter table 表名 add [unique | fulltext | spatial] index 索引名 (字段名[(长度)] [asc | desc]));
事务
事务指逻辑上的一组操作，组成操作的各个单元，要不全部成功，要不全部成功，要不全部不成功。
数据库开启事务命令：
start transaction; /*开启事务*/ rollback; /*回滚事务，即撤销指定的sql语句(只能回退insert delete update语句)*/ commit; /*提交事务，提交为存储的事务*/ savepoint point_name; /*保留点，事务处理中设置的临时占位符，你可以对它发布回退(与整个事务回退不同)*/ pollback to point_name; /*退回到保留点的操作位置*/ insert into table_name (field1,field2) values (1,2),(1,9); savepoint insert1; insert into table_name (field1,field2) values (2,2),(3,9); savepoint insert2; delete from table_name where field=2; savepoint delete1; rollback to insert2;
mysql创建函数
delimiter $$ /*修改mysql的语句结束符，默认是以;号结束的。*/ create procedure autoinsert() begin delclare i int default 1; while (i<50000) do insert into table_name values(i,'abc'); set i=i+1; end while; end$$ delimiter ; /*把语句结束符修改为mysql默认的;号*/ --调用函数 call autoinsert();
浙公网安备 33010602011771号