时间：2021-07-01 10:21:17 帮助过：6人阅读
ALTER TABLE tb_name;
ALTER TABLE tb_name ADD 字段名字 字段类型 约束条件 [FIRST/AFTER 字段名称]
1>添加用户名字段
ALTER TABLE test ADD username VARCHAR(20);
2>将字段test添加到表中第一个字段
ALTER TABLE test ADD test VARCHAR(20) FIRST;
3>在id之后添加年龄字段
ALTER TABLE test ADD age TINYINT UNSIGNED AFTER id;
4>一次添加多个字段
ALTER TABLE test ADD test1 INT UNSIGNED NOT NULL,
ADD test2 TINYINT UNSIGNED NOT NULL DEFAULT 12,
ADD test3 INT;
ALTER TABLE tb_name DROP [COLUMN] 字段名称;
将test1字段删除
ALTER TABLE test DROP COLUMN test1;
将test字段删除
ALTER TABLE test DROP test;
将test2,test3,字段删除,添加test4
ALTER TABLE test DROP test2,DROP test3,ADD test4 INT;
ALTER TABLE tb_name MODIFY 字段名称 字段类型 约束条件 [FIRST|AFTER 字段名称];
将test4字段变为VARCHAR(20) NOT NULL DEFAULT 'THIS IS A TEST' FIRST
ALTER TABLE test MODIFY test4 VARCHAR(20) NOT NULL DEFAULT 'THIS IS A TEST' FIRST;
ALTER TABLE tb_name CHANGE 原字段名称 新字段名称 字段类型 约束条件 [FIRST|AFTER 字段名称]
将test4修改为test2
ALTER TABLE test CHANGE test4 test2 VARCHAR(32);(必须给新名称指定类型）
将test2修改为test2 数据类型 INT NOT NULL DEFAULT 100
ALTER TABLE test CHANGE test2 test2 INT NOT NULL DEFAULT 100;
将id字段修改为主键并自增长
ALTER TABLE test CHANGE id id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;
将test2的字段位置改在username 之后
ALTER TABLE test CHANGE test2 test2 INT UNSIGNED NOT NULL DEFAULT 100 AFTER username;
ALTER TABLE tb_name ADD PRIMARY KEY(字段名称）
CREATE TABLE test1(
id TINYINT);
将id字段添加为主键
ALTER TABLE test1 ADD PRIMARY KEY(id);
ALTER TABLE test1 ADD CONSTRAINT symbol PRIMARY KEY(id);
ALTER TABLE tb_name DROP PRIMARY KEY;
删除TEST1的主键
ALTER TABLE test1 DROP PRIMARY KEY;
CREATE TABLE test2(
id TINYINT UNSIGNED AUTO_INCREMENT KEY
);
删除test2主键
ALTER TABLE test2 MODIFY id TINYINT UNSIGNED
ALTER TABLE test2 DROP PRIMARY KEY;
ALTER TABLE tb_name ADD UNIQUE [KEY|INDEX][index_name](字段名称)
age字段唯一
ALTER TABLE test ADD UNIQUE (age);
将username字段添加成唯一，同时指定索引名称
ALTER TABLE test ADD UNIQUE KEY un_username (username);
ALTER TABLE tb_name DROP INDEX index_name;
age索引删除
ALTER TABLE test DROP INDEX age;
username字段唯一索引删除
ALTER TABLE test DROP INDEX un_username;
alter table tb_name ALTER 字段名称 SET DEFAULT 值；
username字段添加
ALTER TABLE test ALTER username SET DEFAULT 'KING';
ALTER TABLE tb_name ALTER 字段名称 DROP DEFAULT;
username删除
ALTER TABLE test ALTER username DROP DEFAULT;
ALTER TABLE tb_name RENAME [TO|AS] new_name;
RENAME TABLE tb_name TO new_name;
test表命名kaikeba
ALTER TABLE test RENAME TO kaikeba;
kaikeba表命名test
ALTER TABLE kaikeba RENAME test;
test表命名ran
RENAME TABLE test TO ran;
ALTER TABLE tb_name ENGINE=存储引擎名称；
ALTER TABLE test ENGINE=MyISAM;
ALTER TABLE tb_name AUTO_INCREMENT=值；
ran表AUTO_INCREMENT=100
ALTER TABLE ran AUTO_INCREMENT=100;
INSERT [INTO] tb_name [(字段名称……)]VALUES(值|exp|)
DEFAULT……）
CREATE TABLE IF NOT EXISTS user(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
age TINYINT UNSIGNED
)ENGINE=InnoDB CHARSET='UTF8'
--插入记录
INSERT INTO user VALUES(1,'2E',2);
指定字段名称
INSERT user(username,age) VALUES('DSA',9);
INSERT user(id,username,age) VALUES(NULL,'DSESA',9);
INSERT user(id,username,age) VALUES(DEFAULT,'SDA',4+3);
一次插入多听记录
INSERT [INTO] tb_name [(字段名称……)]VALUES(值……)，(值……)
INSERT INTO user(username,age) VALUES ('A',2),('W',9),('21',8);
--通过SET插入记录
INSERT [INTO] tb_name SET 字段名称=值，……；
INSERT user SET username='sadfl',age=9;
UPDATE tb_name SET 字段名称=值|EXP|DEFAULT [WHERE 条件]
不加条件会更新表中所有记录
--user表中第一用户年龄改为45
UPDATE user SET age=45 WHERE id=1;
UPDATE user SET age=45,username='iopo' WHERE id=1;
添加默认值
ALTER TABLE user ALTER age SET DEFAULT 90;
--将user表中第三个用户的年龄改为默认值
UPDATE user SET age=DEFAULT WHERE id=3;
DELETE FROM tb_name [WHERE 条件]
--删除id为1
DELETE FROM user WHERE id=1;
--删除所有记录
DELETE FROM user;
ALTER TABLE user AUTO_INCREMENT=1;
重置AUTO_INCREMENT
5>彻底清空一个表TRUNCATE,清空表时不用加条件会重置AUTO_INCREMENT,
TRUNCATE TABLE tb_name;
TRUNCATE TABLE user;
SELECT exp[,..] FROM tb_name
[WHERE 条件]
[GROUP BY 分组[HAVING 子句对分组结果进行二次筛选]]
[ORDER BY 排序]
[LIMIT 限制显示条数]
SELECT * FROM user;
*代表所有字段
SELECT 字段名称，字段名称 FROM tb_name;
SELECT username FROM user;
SELECT username,age FROM user;
SELECT 表名.字段名称，...FROM tb_name;
SELECT user.id,user.age,user.username FROM user;
给字段起别名，给表起别名
SELECT 字段名称 [AS] 别名，...FROM tb_name [AS] 别名
SELECT id AS '编号',username AS '用户名',age AS '年龄'FROM user;
别名时尽量不要省略AS
SELECT u.id,u.username,u.age FROM user AS u;
比较运算符
SELECT 字段名称...FROM tb_name
WHERE 条件；
比较运算符
> >= < <= = <=> != <>
--user表插入记录
INSERT user VALUES(11,'EWRR',15);
INSERT user VALUES(6,'EWR44R',14);
INSERT user VALUES(7,'EW56RR'，45);
INSERT user VALUES(8,'EUYUYR',55);
INSERT user VALUES(9,'EWRR',4);
INSERT user VALUES(12,'EWXR4R',5);
INSERT user VALUES(4,'EWSRR',9);
--用户名为'sad'
SELECT * FROM user WHERE username='EWRR';
SELECT * FROM user WHERE id=6;
--年龄大于18
SELECT * FROM user WHERE age>=18;
不等于
SELECT * FROM user WHERE age!=123;
SELECT * FROM user WHERE age<>123;
等于
SELECT * FROM user WHERE age<=>123;
//<=>可以检测某个值是否为空
INSERT user VALUES(36,'test1',NULL);
SELECT * FROM user WHERE age<=>NULL;
SELECT * FROM user WHERE age=NULL;
--通过IS NULL 或者IS NOT NULL检测是否为空
SELECT * FROM user WHERE age IS NULL;
SELECT * FROM user WHERE age IS NOT NULL;
--年龄18-28
BETWEEN...AND或者NOT BETWEEN...AND
SELECT * FROM user WHERE age BETWEEN 18 AND 28;
SELECT * FROM user WHERE age NOT BETWEEN 18 AND 28;
IN在集合中的形式
SELECT * FROM user WHERE age IN(18 ,12,23,35,28);
SELECT * FROM user WHERE age NOT IN(18 ,12,23,35,28);
通过LIKE实现模糊查询
%：代表0个1个或多个任意字符；
—：1个任意字符
--包含张/w的用户
SELECT * FROM user WHERE username LIKE '%张%';
SELECT * FROM user WHERE username LIKE '%W%';
--以张开头
SELECT * FROM user WHERE username LIKE '张%';
查询用户名长度为3位的用户
SELECT * FROM user WHERE username LIKE '___';
SELECT * FROM user WHERE username LIKE '_E%';
逻辑运算符
and or
用户名为king并且年龄为28的信息
SELECT * FROM user WHERE username='king' AND age=28;
用户名包含k或者年龄小于50的用户
SELECT * FROM user WHERE username LIKE '%K%' OR age<50;
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depname VARCHAR(20) NOT NULL UNIQUE);
INSERT department(depname) VALUES('摄影部');
INSERT department(depname) VALUES('视频部');
INSERT department(depname) VALUES('教学部');
INSERT department(depname) VALUES('督导部');
CREATE TABLE IF NOT EXISTS employee(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT '北京',
salary FLOAT(6,2) NOT NULL DEFAULT 0,
sex ENUM('男','女','保密'),
depid TINYINT UNSIGNED
);
INSERT employee(username,age,addr,salary,sex,depid)
VALUES('张三',12,'上海',6234.56,'男',1);
INSERT employee(username,age,addr,salary,sex,depid)
VALUES('张三风',22,'北京',1563.51,'男',2);
INSERT employee(username,age,addr,salary,sex,depid)
VALUES('章子怡',14,'云南',6454.14,'女',1);
INSERT employee(username,age,addr,salary,sex,depid)
VALUES('丽丽',41,'广州',1201.45,'男',2);
INSERT employee(username,age,addr,salary,sex,depid)
VALUES('李成',52,'深圳',6914.86,'男',2);
INSERT employee(username,age,addr,salary,sex,depid)
VALUES('程程',25,'成都',5631.12,'女',3);
INSERT employee(username,age,addr,salary,sex,depid)
VALUES('白静',65,'南阳',4823.45,'男',4);
INSERT employee(username,age,addr,salary,sex,depid)
VALUES('程秀碧',48,'广汉',5523.36,'女',4);
按性别分组
SELECT * FROM employee GROUP BY sex;
按地址分组
SELECT * FROM employee GROUP BY addr;
按部门编号
SELECT * FROM employee GROUP BY depid;
根据性别和地址分组
SELECT * FROM employee GROUP BY sex,addr;
SELECT * FROM employee GROUP BY addr,sex;
查询结果跟顺序有关
--分组查询配合GROUP_CONCAT()
--按性别分组，得到每个组中人员的名称
SELECT * ,GROUP_CONCAT(username) FROM employee GROUP BY sex;
SELECT * ,GROUP_CONCAT(username) AS usernames FROM employee GROUP BY sex;
--配合聚合函数使用
COUNT():统计记录数目
SUM():求字段和
AVG():求字段平均值
MAX():
MIN():
--统计员工表中员工数目，以及薪水总和、最大最小值
SELECT id AS '编号',username AS '用户名',COUNT(*) AS 'totalNUMS',SUM(salary) AS '总薪水'
,AVG(salary) AS '平均薪水',MAX(salary) AS '最高薪水',MIN(salary) AS '最低薪水'FROM employee;
SELECT id AS '编号',username AS '用户名',COUNT(*) AS 'totalNUMS',SUM(salary) AS '总薪水'
,AVG(salary) AS '平均薪水',MAX(salary) AS '最高薪水',MIN(salary) AS '最低薪水'FROM employee\G;
COUNT(*),COUNT(字段名称):
*无论字段值是否为NULL都统计
字段名称：不统计NULL记录。
INSERT employee(username,age,addr,salary,sex,depid)
VALUES('程秀碧',NULL,'广汉',5523.36,'女',4);
SELECT COUNT(*) FROM employee;
SELECT COUNT(age) FROM employee;
--按性别分组，统计出每个组找那个年龄最大最小，最高薪水，每个组人数，以及平均薪水
SELECT id,sex,MAX(age) AS max_age,MIN(age) AS min_age,MAX(salary) AS max_salary,COUNT(*) AS total,AVG(salary) AS avg_salary FROM employee GROUP BY sex;
SELECT id,sex,MAX(age) AS max_age,MIN(age) AS min_age,MAX(salary) AS max_salary,COUNT(*) AS total,AVG(salary) AS avg_salary,GROUP_CONCAT(username) AS users FROM employee GROUP BY sex;
--按照性别分组
SELECT id,sex,COUNT(*) AS total FROM employee GROUP BY sex;
分组后组中人数大于3的有
SELECT id,sex,COUNT(*) AS total FROM employee GROUP BY sex HAVING COUNT(*)>6;
--按地址分组
SELECT id,addr,MIN(age) AS min_age FROM employee GROUP BY addr;
分组后要求组中最小人员年龄大于18；
SELECT id,addr,MIN(age) AS min_age FROM employee GROUP BY addr HAVING MIN(age)>25;
SELECT exp FROM tb_name
[WHERE 条件]
[GROUP BY 分组[HAVING对分组结果进行二次筛选]]
[ORDER BY 字段名称]
[LIMIT 限制显示条数 ]
ORDER BY 字段名称|位置 ASC|DESC;
--按照ID由小到大排序
SELECT * FROM department ORDER BY id ASC;
SELECT * FROM department ORDER BY id ;
默认升序，所以查询结果一样
SELECT * FROM department ORDER BY id DESC;
--按位置
SELECT * FROM department ORDER BY 1 DESC;
SELECT * FROM employee ORDER BY age DESC;
SELECT * FROM employee ORDER BY 3 DESC;
当有相同年龄时，按出现顺序进行排序
--按多个字段排序
ORDER BY 字段名称1，字段名称2
SELECT * FROM employee ORDER BY age DESC,id DESC;
SELECT * FROM employee ORDER BY id DESC,age DESC;
查询结果与字段名称的顺序有关
LIMIT 值：
代表显示前几条记录
LIMIT 偏移量，显示记录条数
实现分页的核心是通过LIMIT语句
--显示前5条
SELECT * FROM employee LIMIT 5;
SELECT * FROM employee ORDER BY id DESC LIMIT 5;
第一条记录偏移量为0
SELECT * FROM employee LIMIT 0, 5;
--下一页，显示后五条
SELECT * FROM employee LIMIT 5, 5;
SELECT * FROM employee LIMIT 10, 5;
--所有条件
SELECT *,GROUP_CONCAT(username) FROM employee WHERE id>2 GROUP BY sex HAVING COUNT(*)>6 ORDER BY age DESC,id DESC LIMIT 0,5;
SELECT * FROM employee WHERE depid in(SELECT id FROM department);
SELECT * FROM employee WHERE depid NOT IN(SELECT id FROM department);
SELECT * FROM employee WHERE depid=(SELECT id FROM department WHERE depname='摄影部');
--
SELECT depname FROM department WHERE id IN(SELECT depid FROM employee WHERE age>25);
--摄影部年龄最小/大值
SELECT MIN(age) FROM employee WHERE depid=1;12
SELECT MAX(age) FROM employee WHERE depid=1;14
--年龄大于摄影部年龄最大的员工
SELECT * FROM employee WHERE age>(SELECT MAX(age) FROM employee WHERE depid=1);
SELECT id FROM department WHERE depname='国防部';空
SELECT * FROM employee WHERE EXISTS(SELECT id FROM department WHERE depname='国防部');
SELECT * FROM employee WHERE EXISTS(SELECT id FROM department WHERE depname='督导部');
>=ANY|SOME:大于子查询中的最小值
>=ALL:大于子查询中的最大值
SELECT * FROM employee WHERE depid>ANY(SELECT id FROM department);depid>2
SELECT * FROM employee WHERE depid>SOME(SELECT id FROM department);depid>2
SELECT * FROM employee WHERE id>ALL(SELECT id FROM department);id>5
<=ANY|SOME:小于子查询中的最大值
<=ALL:小于子查询中的最小值
=ANY|SOME:相当于IN,不能写不等于
SELECT * FROM employee WHERE depid=ANY(SELECT id FROM department);全部成员
<>ALL:相当于NOT IN
SELECT * FROM employee WHERE depid<>ALL(SELECT id FROM department);空
等于：只能ANY|SOME;不等于：ALL只能。
开发中很少用子查询，效率不高，必须现执行子查询，再执行外层查询。
连接查询：内连接+外连接
外连接：左连接+右连接
--员工表中员工姓名、编号、性别、年龄、所属部门名称
SELECT e.id,e.username,e.sex,e.age,d.depname FROM employee AS e,department AS d;得到两边笛卡尔积
由于哪个桥梁连接起来的
员工表中的depid等于部门表的id
SELECT e.id,e.username,e.sex,e.age,d.depname FROM employee AS e,department AS d WHERE e.depid=d.id;
1>内连接
[INNER|CROSS] JOIN
通过ON连接条件
查询出两个或多个表都满足的结果
--员工表所属部门名称
SELECT e.id,e.username,e.sex,e.age,d.depname FROM department AS d INNER JOIN employee AS e ON e.depid=d.id;
WHERE通常用于条件塞选
--员工年龄大于25
SELECT e.id,e.username,e.sex,e.age,d.depname FROM department AS d INNER JOIN employee AS e ON e.depid=d.id WHERE e.age>25;
CREATE TABLE IF NOT EXISTS provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
pname VARCHAR(10) NOT NULL UNIQUE
);
INSERT provinces(pname) VALUES('四川'),('云南'),('陕西'),('广东');
ALTER TABLE employee ADD addr1 TINYINT UNSIGNED DEFAULT 1;
UPDATE employee SET addr1=2 WHERE id IN(2,4,6,8);
ALTER TABLE employee CHANGE addr1 pid TINYINT UNSIGNED DEFAULT 1;
UPDATE employee SET pid=2 WHERE id IN(2,4,6,8);
--查询员工所属省份
SELECT e.id,e.username,p.pname FROM employee AS e JOIN provinces AS p ON p.id=e.pid;
--部门名称、省份名称、用户的信息
SELECT e.id,e.username,p.pname,d.depname FROM employee AS e JOIN provinces AS p ON p.id=e.pid JOIN department AS d ON e.depid=d.id;
不断在后面加JOIN跟上ON条件。
SELECT e.id,e.username,p.pname,d.depname FROM employee AS e JOIN provinces AS p ON p.id=e.pid JOIN department AS d ON e.depid=d.id LIMIT 0,3;
INSERT employee(username,age,addr,salary,sex,depid,pid) VALUES('test5',25,'北京',6542,'女',6,9);
插入成功了，但这条数据部合法。省份只有4个，部门只有4个，没有保证记录完整性与一致性，那么就应当通过外键来实现。
依赖已存在表的主键来设置外键。
作用：保证记录完整性与一致性
创建外键注意事项：
父表与子表必须使用相同的存储引擎，禁止使用临时表；
存储引擎只能为InnoDB
子表外键必须关联父表主键
外键列与参照列应具有相似的数据类型。
1》创建外键
创建表时
CREATE TABLE IF NOT EXISTS user(
id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE,
pid TINYINT UNSIGNED ,
FOREIGN KEY(pid) REFERENCES provinces(id)
);
CONSTRAINT 约束名称 FOREIGN KEY(外键列) REFERENCES 主表（字段）；
CREATE TABLE IF NOT EXISTS user1(
id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE,
pid TINYINT UNSIGNED ,
CONSTRAINT user1_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id)
);
INSERT user(username,pid) VALUES('DSFSA',1);
INSERT user(username,pid) VALUES('ZDX',2);
INSERT user(username,pid) VALUES('SSA',3);
INSERT user(username,pid) VALUES('HGA',4);
INSERT user(username,pid) VALUES('HGRE',5);省份没有id=5,插入失败
DELETE FROM provinces WHERE id=4;不成功，id=4的省份还有用户，应当先删除用户，在删除省份。
SELECT * FROM user;
DELETE FROM user WHERE pid=4;
DELETE FROM provinces WHERE id=4;
DELETE FROM provinces WHERE id=3;一样不成功，原因同id=4，有外键约束存在：
下面删除外键：
ALTER TABLE tb_name DROP FOREIGN KEY 约束名称；
--查看user约束名称
SHOW CREATE TABLE user;
--删除外键约束
ALTER TABLE user DROP FOREIGN KEY user_ibfk_1;
--现在做删除操作，成功
DELETE FROM provinces WHERE id=3;
添加外键
ALTER TABLE tb_name ADD CONSTRAINT 约束名称 FOREIGN KEY(字段） REFERENCES 主表（字段）；
ALTER TABLE user ADD CONSTRAINT user_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id);不成功
DELETE FROM user WHERE pid=3;
ALTER TABLE user ADD CONSTRAINT user_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id);删除pid=3的记录后添加成功
先删除子表记录，再删除父表记录
创建外键的时候可以指定一下，当我们删除父表记录时，子表应当进行什么样的操作。
CASCADE,级联
SET NULL(保证列没有非空约束)
RESTRICT
NO ACTION效果同RESTRICT
ON UPDATE|ON DELETE CASCADE|SET NULL|RESTRICT|NO ACTION
--测试CASCADE
ALTER TABLE user DROP FOREIGN KEY user_fk_provinces;
SHOW CREATE TABLE user;
ALTER TABLE user ADD CONSTRAINT user_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE;
DELETE FROM provinces WHERE id=2;实际上先删除user中的pid=2的记录，再删除provinces表中的记录。
实际开发中采用外键思想进行操作，而不是纯的物理外键
LEFT|RIGHT [OUTER] JOIN ON 条件
LEFT:显示左表中的全部记录和右表中符合条件的记录
RIGHT:显示右表中的全部记录和左表中符合条件的记录
--内连接
SELECT e.id,e.username,d.depname FROM employee AS e JOIN department AS d ON e.depid=d.id;
--外连接
SELECT e.id,e.username,d.depname FROM employee AS e LEFT JOIN department AS d ON e.depid=d.id;
当右表中没有满足条件时，NULL填充
SELECT e.id,e.username,d.depname FROM employee AS e RIGHT JOIN department AS d ON e.depid=d.id;
内连接查询两个或多个表中都符合条件的记录
INSERT department(depname) VALUES('测试部门');
SELECT e.id,e.username,d.depname FROM employee AS e RIGHT JOIN department AS d ON e.depid=d.id;
多个表中的记录和在一起
UNION ALL，简单地将查询结果合并到一起
UNION会去掉重复记录
SELECT * FROM employee;9
SELECT * FROM user;1
SELECT username FROM user UNION ALL SELECT username FROM employee;
联合查询，字段顺序、字段数目一定要相同
SELECT id,username,age FROM employee UNION ALL SELECT id AS uid FROM user;不成功
SELECT id,username,age FROM employee UNION ALL SELECT id AS uid,username AS uname,1 FROM user;
--将employee表中的addr存为省份表中的id
UPDATE employee AS e JOIN provinces AS p ON e.pid=p.id SET e.addr=p.id;
SELECT * FROM employee;
--删除所有所在省份在省份表中省份的人
DELETE e FROM employee AS e JOIN provinces AS p ON e.pid=p.id;
多表操作，注意表和表间的连接条件
删除数据表
一张表
DROP TABLE tb_name;
多张表
DROP TABLE tb_name1,tb_name;