01.MySQL基础语法与约束

SQL语法

SQL语句可以在单行或多行书写，以分号结尾(在命令行里)
可使用空格和缩进来增强语句的可读性
MySQL不区别大小写，建议使用大写

SQL语句分类

DDL (Data Definition Language)：数据库或表的结构操作
DML (Data Manipulation Language)：对表的记录进行更新（增、删、改）
DQL (Data Query Language)：对表的记录的查询
DCL (Data Control Language)：对用户的创建，及授权

DDL (Data Definition Language)

数据库操作：

查看所有数据库：SHOW DATABASES
选择要操作的数据库：USE 数据库名
创建数据库(方括号中是可选操作)：CREATE DATABASE [IF NOT EXISTS] mydb1 [CHARSET=utf8]
删除数据库：DROP DATABASE [IF EXISTS] mydb1
修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

表操作：

创建表

CREATE TABLE [IF NOT EXISTS] 表名(
    列名 列类型,
    列名 列类型,
    ...
    列名 列类型
);

查看当前数据库中所有的表名称：SHOW TABLES
查看表结构：DESC 表名
查看建表语句：SHOW CREATE TABLE 表名
删除表：DROP TABLE 表名
修改表：ALTER TABLE 表名
- 添加列：
```
ALTER TABLE 表名
ADD (
    列名 列类型,
    列名 列类型,
    ...
    列名 列类型
);
```
- 修改列类型：ALTER TABLE 表名 MODITY 列名列类型;
- 修改列名：ALTER TABLE 表名 CHANGE 原列名新列名列类型;
- 删除列：ALTER TABLE 表名 DROP 列名;
- 修改表名称：ALTER TABLE 原表名 RENAME TO 新表名;

DML (Data Manipulation Language)

插入数据：

INTERT INTO 表名(列名1,列名2, ...) VALUES(列值1, 列值2, ...);
INTERT INTO 表名 VALUES(列值1, 列值2)

示例：

// 插入所有列
INSERT INTO stu(
  number, name, age, gender
)
VALUES(
  'ITCAST_0001', 'zhangSan', 28, 'male'
);
 
// 插入部分列，没有指定的列默认为NULL值
INSERT INTO stu(
  number, name
) VALUES(
  'ITCAST_0002', 'liSi'
)
 
// 不给出插入列，那么默认为插入所有列！值的顺序要与创建表时列的顺序相同
INSERT INTO stu VALUES(
  'ITCAST_0003', 'wangWu', 82, 'female'
);

修改数据：

UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... [WHERE 条件]
- 条件必须是一个 boolean 类型的值或表达式：UPDATE t_person SET gender='男', age=age+1 WHERE sid='1';
- 运算符：=, !=, <>, >, <, >=, <=, BETWEEN...AND, IN(...), IS NULL, NOT, OR, AND

示例：

/* 下面两句效果是一样的 */
UPDATE stu SET age=102 WHERE age >= 18 AND age <= 80;
UPDATE stu SET age=102 WHERE age BETWEEN 18 AND 80;
 
UPDATE stu SET age=102 WHERE name='zhangSan' OR name='liSi';
UPDATE stu SET age=102 WHERE name IN ('zhangSan', 'liSi');
UPDATE stu SET age=102 WHERE age IS NULL;  /* 不能使用等号(age=null)，age=null返回的都是false */
UPDATE stu SET age=102 WHERE age IS NOT NULL;

删除数据：

DELETE FROM 表名 [WHERE 条件];，一定要带 where 条件！要不就把整个表里的数据全删了！

DQL (Data Query Language)

SQL中单表查询关键字及其排列顺序：

select
from
where
group by
having
order by

基本查询

//表名：emp
//查询所有列
SELECT * FROM emp;
 
//查询指定列
SELECT empno, ename, sal, comm FROM emp;
 
//查询sal列，如果相同只显示一次
SELECT DISTINCT sal FROM emp;
 
//列运算
//加、减、乘、除运算
SELECT sal*1.5 FROM emp;
SELECT sal+comm FROM emp;
 
//字符串连接
SELECT CONCAT('$', sal) FROM emp;
 
//如果comm中存在NULL值，那么当成0来运算
SELECT IFNULL(comm, 0)+1000 FROM emp;
 
//给列起别名
SELECT IFNULL(comm, 0)+1000 AS 奖金 FROM emp;  /* 其中AS可以省略 */
/* 加了别名之后，显示出来的表的表头会显示为别名奖金 */
/* 不加别名表头会显示为：IFNULL(comm, 0)+1000 */

条件控制

//条件查询
SELECT empno,ename,sal,comm FROM emp WHERE sal > 10000 AND comm IS NOT NULL;
SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;
SELECT empno,ename,job FROM emp WHERE job IN ('经理', '董事长');

IN 的固定列表、子查询、NULL 陷阱和索引使用详见 03.MySQL IN 详解。

模糊查询

//模糊查询，'_'匹配一个字符，'%'匹配0~N个字符
SELECT * FROM emp WHERE ename LIKE '张_';
SELECT * FROM emp WHERE ename LIKE '张%';
SELECT * FROM emp WHERE ename LIKE '%';  //这个条件等同与不存在，但如果姓名为NULL的查询不出来！

排序

//按sal排序，升序，ASC可以省略
SELECT * FROM WHERE emp ORDER BY sal ASC;
 
//按comm排序，降序
SELECT * FROM WHERE emp ORDER BY comm DESC;
 
//使用sal升序排，如果sal相同时，使用comm的降序排列
SELECT * FROM WHERE emp ORDER BY sal ASC, comm DESC;

聚合函数

//COUNT
//计算emp表中所有列都不为NULL的记录的行数
SELECT COUNT(*) FROM emp;
 
//计算emp表中comm列不为NULL的记录的行数
SELECT COUNT(comm) FROM emp;
 
//MAX
//查询最高工资
SELECT MAX(sal) FROM emp;
 
//MIN
//查询最低工资
SELECT MIN(sal) FROM emp;
 
//SUM
//查询工资和
SELECT SUM(sal) FROM emp;
 
//AVG
//查询平均工资
SELECT AVG(sal) FROM emp;

分组查询

分组查询常和聚合函数一起使用。

//使用deptno分组，查询部门编号和每个部门的记录数
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
 
//使用job分组，查询每种工作的最高工资
SELECT job, MAX(SAL) FROM emp GROUP BY job;
 
//以部门分组，查询每组记录数，条件为记录数大于3
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;

limit 子句

//LIMIT用来限定查询结果的起始行，以及总行数
//查询起始行为第5行，一共查询3行记录
SELECT * FROM emp LIMIT 4, 3;

单表查询练习题

/* 1. 查询出部门编号为30的所有员工。*/
SELECT empno FROM emp WHERE deptno = 30;
 
/* 2. 所有销售员的姓名、编号和部门编号。*/
SELECT ename,empno,deptno FROM emp WHERE job = '销售员';
 
/* 3. 找出奖金高于工资的员工。*/
SELECT * FROM emp WHERE comm > sal;
 
/* 4. 找出奖金高于工资60%的员工。*/
SELECT * FROM emp WHERE comm > 0.6 * sal;
 
/* 5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。*/
SELECT * FROM emp WHERE (deptno = 10 AND job = '经理') OR (deptno = 20 AND job = '销售员');
 
/* 6. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。*/
SELECT * FROM emp WHERE (deptno = 10 AND job = '经理') OR (deptno = 20 AND job = '销售员') OR (job NOT IN ('经理', '销售员') AND sal >= 20000);
 
/* 8. 无奖金或奖金低于1000的员工。*/
SELECT ename FROM emp WHERE comm IS NULL OR comm < 1000;
 
/* 9. 查询名字由三个字组成的员工。*/
SELECT * FROM emp WHERE ename LIKE '___';
 
/* 10.查询2000年入职的员工。*/
SELECT * FROM emp WHERE hiredate LIKE '2000-%';
 
/* 11. 查询所有员工详细信息，用编号升序排序。*/
SELECT * FROM emp ORDER BY empno;
 
/* 12. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序。*/
SELECT * FROM emp ORDER BY sal DESC, hiredate;
 
/* 13. 查询每个部门的平均工资。*/
SELECT deptno, AVG(sal) 平均工资 FROM emp GROUP BY deptno;
 
/* 14. 查询每个部门的雇员数量。 */
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
 
/* 15. 查询每种工作的最高工资、最低工资、人数。*/
SELECT job, MAX(sal), MIN(sal), COUNT(*) FROM emp GROUP BY job;

约束

主键约束

主键是唯一表示，有如下三个特性：

非空
唯一
被引用

创建表时指定主键：

/* Method 1 */
CREATE TABLE stu(
    sid CHAR(6) PRIMARY KEY,
    sname VARCHAR(20)
);
/* Method 2 */
CREATE TABLE stu(
    sid CHAR(6),
    sname VARCHAR(20),
    PRIMARY KEY(sid)
);

修改表时指定主键：

ALTER TABLE stu ADD PRIMARY KEY(sid);

修改表时删除主键：

ALTER TABLE stu DROP PRIMARY KEY; /* 因为主键是唯一的，所以删除时不用指定是哪一列 */

设置主键自增长

创建表时指定主键自增长：

CREATE TABLE stu(
    sid CHAR(6) PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(20)
);

修改表时设置主键自增长 (和修改列名差不多)：

ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

修改表时删除主键自增长 (和修改列名差不多)：

ALTER TABLE stu CHANGE sid sid INT;

非空约束

CREATE TABLE stu(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(20) NOT NULL  /* 对sname列设置了非空约束 */
);

唯一约束

CREATE TABLE stu(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(20) NOT NULL UNIQUE  /* 对sname列设置了非空约束 */
);

Afyke的个人博客

探索