[xxx],方框表示选填
表
1、创建表
CREATE TABLE tab_name(
field1 type[完整约束条件],
field2 type[完整约束条件],
field3 type[完整约束条件],
...
fieldx type[完整约束条件]
)[CHARACTER SET xxx];
①最后一个字段 不需要逗号
②完整约束条件有 :
PRIMARY KEY 主键(非空且唯一)
NOT NULL 非空
AUTO_INCREMENT 自增
DEFAULT xxx 默认值
UNIQUE 唯一
举例:
CREATE TABLE teacher(
id TINYINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
entry_date DATE DEFAULT "2019-02-13",
gender BOOLEAN,
salary DOUBLE(7,2) UNSIGNED
);
--BOOLEAN,为 0 or 1
--DOUBLE(7,2),总共 7 位,2 位小数。
--UNSIGNED 非负数,只针对整型,可以将数据范围扩大一倍
2、查看表
--查看当前数据库下的所有表
SHOW TABLES;
--查看当前数据库表的建表语句
SHOW CREATE TABLE tab_name;
--查看表结构
DESC tab_name;
3、删除表
DROP TABLE tab_name;
4、修改表、字段
--重命名表名称
RENAME TABLE tab_name to new_name;
--增加字段(列)
ALTER TABLE tab_name ADD field type [约束条件][FIRST|AFTER 字段];
--修改字段名称
ALTER TABLE tab_name CHANGE field new_field type [约束条件][FIRST|AFTER 字段];
--修改字段类型
ALTER TABLE tab_name MODIFY field type [约束条件][FIRST|AFTER 字段];
--删除字段
ALTER TABLE tab_name DROP field;
举例:
RENAME TABLE teacher TO teacher1;
ALTER TABLE teacher1 ADD age INT DEFAULT 25 AFTER id;
ALTER TABLE teacher1 CHANGE entry_date entry_day DATE DEFAULT "2019-01-01";
ALTER TABLE teacher1 MODIFY age TINYINT,
DROP salary;
注意:
ALTER TABLE teacher1 MODIFY id INT PRIMARY KEY AUTO_INCREMENT;
--这句会出错,不能设置多个主键,所以若是对主键进行修改,不需要重复设置 PRIMARY KEY,直接修改即可。
ALTER TABLE teacher1 MODIFY id INT AUTO_INCREMENT;
数据
1、插入数据
--单条插入
INSERT INTO tab_name(field1,field2...)VALUES(xx,xx...);
--多条插入
INSERT INTO tab_name(field1,field2...)VALUES(xx,xx...),
(xx,xx...),
(xx,xx...);
--省略字段即默认所有字段
INSERT INTO tab_name VALUES(xx,xx....);
--使用 SET 来插入数据
INSERT INTO tab_name SET field1=xx,field2=xx;
举例:
INSERT INTO teacher1(id,name,gender)VALUES(1,"a",1),
(2,"b",0),
(3,"c",1),
(4,"d",1);
INSERT INTO teacher1 SET id=5,name="e",gender=0;
2、修改数据
--若不加约束条件则修改整张表
UPDATE tab_name SET field1=xx,field2=xx [约束条件];
--举例
UPDATE teacher1 SET gender=0 WHERE id in (2,3,5);
3、删除表内记录
--若不加约束条件则删除所有记录,但是自增仍然从上一个id开始自增
DELETE FROM tab_name [约束条件];
--删除表(可从事务中恢复)
DROP TABLE tab_name;
--删除表然后新建同一张空表(不可恢复)
TRUNCATE TABLE tab_name
举例:
--全删:
DELETE FROM teacher1;
INSERT INTO teacher1 (name) VALUES ("f");--此处 id 为 6
--条件删除:
DELETE FROM teacher1 WHERE name="e";
4、查找表记录
SELECT [DISTINCT]*|field1,field2 AS alias... FROM tab_name
WHERE xxx
GROUP BY field|n
HAVING xxx
ORDER BY field
LIMIT n;
DISTINCT 去重
AS 别名,用作显示字段
GROUP BY 分组,只显示每组第一个数据
HAVING 条件过滤
ORDER BY 按某个字段排序
LIMIT 限制查询数量
命令执行顺序:
FROM - WHERE - SELECT - GROUP BY - HAVING - ORDER BY - LIMIT
所有 WHERE 都可以被替换为 HAVING,反之不成立。但是在速度和内存上有区别
AS 的别名不能在 WHERE 条件中进行使用;
--整张表
SELECT * FROM teacher1;
-条件过滤
SELECT * FROM teacher1 WHERE id=1;
比较运算符 >,<,=,>=,<=,!=
在xx值之间 BETWEEN 1 AND 4
几个值之一 IN(a,b,d)
模糊匹配 LIKE xx% 或 LIKE xx_
%表示任意多字符,_表示一个字符
逻辑运算符 AND OR NOT
为空 IS NULL
--AS 用作别名,可省略
SELECT name AS 姓名 FROM teacher1 WHERE id IN(1,2,4);
SELECT name 姓名,gender 性别 FROM teacher1;
--分组查找 GROUP BY,可以根据字段名或者第几个字段 分组
SELECT * FROM teacher1 GROUP BY gender,age;
SELECT * FROM teacher1 GROUP BY 4;
--聚合函数,配合分组函数使用
COUNT(field) 统计行的个数
SUM(field) 统计行的内容和
AVG(field) 统计行的平均值
MAX/MIN(field) 统计行的最值
SELECT COUNT(*) FROM teacher1 GROUP BY gender;
SELECT name,SUM(age) FROM teacher1 GROUP BY gender;
SELECT name,SUM(age) FROM teacher1 GROUP BY gender HAVING SUM(age) IS NOT NULL;
--将多个信息在一行中显示
CONCAT_WS(符号,field1,field2...)
SELECT CONCAT_WS('-',id,name,age) AS info FROM teacher1;
--在分组中将多个信息在一行中显示出来
GROUP_CONCAT([DISTINCT]field1 ORDER BY field2 ASC|DESC SEPARATOR "xx")
在分组中将字段1(根据字段2排序,同一行用符号xx连接)在一行中显示出来
SELECT gender,GROUP_CONCAT(DISTINCT name ORDER BY id DESC SEPARATOR "-") AS "muti-info" FROM TEACHER1 GROUP BY gender;
注意:
①命令语句执行顺序:
select *|field1,field2 from tab_name [where];
select distinct name from tab_name where id>1;