MySQL常用语句

一、数据库操作

#查看当前数据库
SELECT DATABASE();
#查看所有数据库
SHOW DATABASES;
#创建库
CREATE DATABASE IF NOT EXISTS db_name charset utf8;

#修改库

ALTER DATABASE 库名 选项信息
#删除库
DROP DATABASE[ IF EXISTS] 数据库名

二、表操作

#创建表
create table table_name(pid INT, name CHAR(20), price INT);
#删除表

#创建表 id 为主键 name 非空
CREATE TABLE table_name(
id int(10) PRIMARY KEY,
name varchar(10) NOT NULL,
sex char(1)
)
#删除表
DROP TABLE IF EXISTS table_name
#清空表数据
TRUNCATE TABLE table_name
#修改表
ALTER TABLE table_name #修改操作
#查看所以表
SHOW TABLES FROM db_name
#复制表结构和数据
CREATE TABLE table_name [AS] SELECT * FROM new_table_name

三、数据操作

#增
INSERT INTO table_name (字段) VALUES(值)
#删
DALETE FROM table_name
#改
UPDATE table_name SET 字段名=新值，字段名=新值
ORDER BY empno DESC #更新条件
#查
SELECT 字段列表 FROM table_name 加查询条件

四、属性约束

#非空约束 唯一约束： unipue

CREATE TABLE tb_user(
id INT AUTO_INCREMENT,
NAME VARCHAR(30) UNIQUE NOT NULL,
age INT,
phone VARCHAR(20) UNIQUE NOT NULL,
email VARCHAR(30) UNIQUE NOT NULL,
PRIMARY KEY (id)
);
#id为自增主键，null值无效，数据库会自动用下一个id值替代

#外键约束 foreign key

CREATE TABLE tb_user_address (

user_id INT PRIMARY KEY NOT NULL,

address VARCHAR(200),

FOREIGN KEY(user_id) REFERENCES tb_user(id)

);

#默认约束 default 检查约束

CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
sex CHAR(2) DEFAULT ‘男’, #默认值
phone CHAR(18),
age INT,
CHECK (age>0 AND age<=200),
createdTime DATE DEFAULT NOW()
);

五、SELECT

#查询指定表
select * from table_name
#查询指定字段
select id,name from table_name
#条件查询
select * from table_name where id=1
select * from table_name where name=‘倾风’
#范围查询
select * from table_name where id between 1 and 4
select * from table_name where id>1 and id<4
#多条件查询
#从from获得的数据源中进行筛选。
#整型1表示真，0表示假。
#表达式由运算符和运算数组成。
#运算数：变量（字段）、值、函数返回值
#运算符：
=, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
is/is not 加上ture/false/unknown，检验某个值的真假
<=>与<>功能相同，<=>可用于null比较
select * from table_name where id=1 or id=4
select * from table_name where id=1 and id=4
#离散查询
select * from table_name where id in(1,2,3,4)
select * from table_name where id not in(1,2,3,4)
#模糊查询
#查询包含 qf
select * from table_name where name like ‘%倾风%’
#查询第二个字符为风的name
select * from table_name where name like ‘%_风%’
#去重 distinct
select * distinct name from table_name
#合并查询 union
select * from table1 where name=‘倾风’
union
select * from table2 where name=‘落霞’
#分页查询
SELECT * FROM emp LIMIT 3 #返回前3条
SELECT * FROM emp LIMIT 0,3 #返回前3条（offset,count）offset从0开始
SELECT * FROM emp LIMIT 1,3 #返回第2到4条，共计3条
#order by 排序

#asc升序

select * from student order by age #升序
select * from student order by age asc #升序
#desc 降序
select * from student order by age desc #降序

六，基础函数

#lower 查询小写 upper 查询大写
select lower(name)，upper(name) from table_name
#length 查询字段长度
select name,length(name) from table_name
#substr 截取字段
select name, substr(name,1,3) from table_name
#concat 连接字符串
select concat(name,’(’,lzx,’)’) from table_name

七，聚合函数

#count 查询记录数
select count(*) from table_name;
select count(1) from table_name;
#max 最大值
select max(id) from table_name;
#min 最小值
select min(id) from table_name;
#avg 平均值
select avg(id) from table_name;
#sum 求和
select sum(id) from table_name;

八，分组

group by 对查询的结果进行分组统计

#group by 对查询的结果进行分组统计

SELECT deptno,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno
ORDER BY MAX(sal)
#having 分组后数据的过滤，就是where语句
SELECT NAME,COUNT(NAME) FROM student
GROUP BY NAME
HAVING COUNT(NAME)>1

九，子查询

/* 子查询 */ ------------------
- 子查询需用括号包裹。
– from型
from后要求是一个表，必须给子查询结果取个别名。
- 简化每个查询内的条件。
- from型需将结果生成一个临时表格，可用以原表的锁定的释放。
#子查询返回一个表，表型子查询。
select * from (select * from tb where id>0) as subfrom where id>1;
– where型
- 子查询返回一个值，标量子查询。
- 不需要给子查询取别名。
- where子查询内的表，不能直接用以更新。
select * from tb where money = (select max(money) from tb);
– 列子查询
如果子查询结果返回的是一列。
使用 in 或 not in 完成查询
exists 和 not exists 条件
如果子查询返回数据，则返回1或0。常用于判断条件。
select column1 from t1 where exists (select * from t2);
– 行子查询
查询条件是一个行。
select * from t1 where (id, gender) in (select id, gender from t2);
行构造符：(col1, col2, …) 或 ROW(col1, col2, …)
行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。
– 特殊运算符
!= all() 相当于 not in
= some() 相当于 in。any 是 some 的别名
!= some() 不等同于 not in，不等于其中某一个。
all, some 可以配合其他运算符一起使用。

十，多表联查

/* 连接查询(join) */ ------------------
将多个表的字段进行连接，可以指定连接条件。
– 内连接(inner join)
- 默认就是内连接，可省略inner。
- 只有数据存在时才能发送连接。即连接结果不能出现空行。
on 表示连接条件。其条件表达式与where类似。也可以省略条件（表示条件永远为真）
也可用where表示连接条件。
还有 using, 但需字段名相同。 using(字段名)
SELECT d.dname,e.ename,e.job
FROM
(SELECT deptno,dname FROM dept) d
INNER JOIN
(SELECT deptno,ename,job FROM emp) e
ON d.deptno = e.deptno
WHERE d.deptno=2

-- 交叉连接 cross join
    即，没有条件的内连接。
    select * from tb1 cross join tb2;

– 外连接(outer join)
- 如果数据不存在，也会出现在连接结果中。
– 左外连接 left join
如果数据不存在，左表记录会出现，而右表为null填充
SELECT d.dname,e.ename,e.job
FROM
(SELECT deptno,dname FROM dept) d
LEFT JOIN
(SELECT deptno,ename,job FROM emp) e
ON d.deptno=e.deptno

-- 右外连接 right join
    如果数据不存在，右表记录会出现，而左表为null填充

select
d.dname,e.ename,e.job
from
(select deptno,dname from dept) d
right join
(select deptno,ename,job from emp) e
on d.deptno = e.deptno

– 自然连接(natural join)
自动判断连接条件完成连接。
相当于省略了using，会自动查找相同字段名。
natural join
natural left join
natural right join
select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;

十一，事务

#多条语句时，批量执行，事务提交 commit
SET @@autocommit = 0;
BEGIN;
INSERT INTO student (id) VALUES(5);
UPDATE student SET NAME=‘倾风’ WHERE id=5;
COMMIT;
#多条语句，批量执行，insert插入重复的主键导致失败，事务回滚rollback
SET @@autocommit = 0;
BEGIN;
INSERT INTO student (id) VALUES(5);
UPDATE student SET NAME=‘倾风’ WHERE id=5;
ROLLBACK;
#保存点 savepoint
SET @@autocommit = 0;# 0 自动 1 手动
SAVEPOINT qing; #设置保存点位置