2021-02-19 MySQL

最新推荐文章于 2024-09-12 01:15:37 发布

caicaioli

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量786

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_45822707/article/details/113855543

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

数据表相关操作
数据的操作(DML)
查询数据操作(DQL)

数据表相关操作

创建数据表

CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [完整性约束条件]
...
)ENGINE=引擎名称 CHARSET='编码方式';

完整性约束条件

PRIMARY KEY主键

CREATE TABLE IF NOT EXISTS user1(
id INT PRIMARY KEY,
username VARCHAR(20)
);

AUTO_INCREMENT自增长

和主键配合使用

CREATE TABLE IF NOT EXISTS user5(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
);
INSERT user5 VALUES(1,'KING');
INSERT user5(username) VALUES('queen1');

INSERT user5 VALUES(111,'KING1');

INSERT user5 VALUES(NULL,'AAAA');

INSERT user5 VALUES(DEFAULT,'AAAA');

NOT NULL非空

CREATE TABLE IF NOT EXISTS user7(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED
);

INSERT user7(username,password) VALUES('KING','KING');

INSERT user7(username,password,age) VALUES('KING1','KING1',12);

INSERT user7(username,password) VALUES(NULL,NULL);

DEFAULT默认值

CREATE TABLE IF NOT EXISTS user8(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT '北京',
sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
);

INSERT user8(username,password) VALUES('KING','KING');

INSERT user8 VALUES(2,'QUEEN','QUEEN',29,'上海','保密');

INSERT user8 VALUES(3,'QUEEN','QUEEN',DEFAULT,DEFAULT,'保密');

UNIQUE KEY唯一

不能重复

CREATE TABLE IF NOT EXISTS user9(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) UNIQUE
);

INSERT user9(username) VALUES('A');
INSERT user9(username,card) VALUES('B','111');

INSERT user9(username,card) VALUES('B1',NULL);

INSERT user9(username,card) VALUES('B2',NULL);

修改表结构？

修改表名

ALTER TABLE tbl_name RENAME [TO|AS] new_name
RENAME TABLE tbl_name TO new_name

ALTER TABLE user10 RENAME TO user11;
ALTER TABLE user11 RENAME AS user10;
ALTER TABLE user10 RENAME user11;
RENAME TABLE user11 TO user10;

添加字段

ALTER TABLE tbl_name ADD 字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]

# 默认添加到已有字段之后
ALTER TABLE user10 ADD card CHAR(18);

ALTER TABLE user10 ADD test1 VARCHAR(100) NOT NULL UNIQUE;
# 添加到指定位置
ALTER TABLE user10 ADD test2 VARCHAR(20) NOT NULL FIRST;
# 添加到某个位置之后
ALTER TABLE user10 ADD test3 INT NOT NULL DEFAULT 100 AFTER username;

选中一次表完成多个操作

ALTER TABLE user10 

ADD test4 INT NOT NULL DEFAULT 123 AFTER password,

ADD test5 FLOAT(6,2) FIRST,

ADD test6 SET('A','B','C');

删除字段

ALTER TABLE tbl_name DROP 字段名称

一次选中，删除多个字段

ALTER TABLE user10 

DROP test2,

DROP test3,

DROP test4,

DROP test5;

一次选中，添加test字段删除addr字段

ALTER TABLE user10 

ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,

DROP addr;

修改字段

ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]

将card字段移动到test字段之后

ALTER TABLE user10 MODIFY card CHAR(18) AFTER test;

将test字段修改为CHAR(32) NOT NULL DEFAULT ‘123’ 移动到第一个位置

ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;

修改字段名称

ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]

将test字段改为test1

ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';

ALTER TABLE user10 CHANGE test1 test VARCHAR(200) NOT NULL AFTER username;

ALTER TABLE user10 CHANGE test test INT;

添加默认值

ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值

ALTER TABLE user11 ALTER age SET DEFAULT 18;

删除默认值

ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT

ALTER TABLE user11 ALTER age DROP DEFAULT;

添加主键

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY[index_type] (字段名称,...)

ALTER TABLE test13 ADD PRIMARY KEY(id,card);

删除主键

ALTER TABLE tbl_name DROP PRIMARY KEY

ALTER TABLE test12 DROP PRIMARY KEY;

ALTER TABLE test13 DROP PRIMARY KEY;

ALTER TABLE test12 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);

删除主键＋自增长
去掉自增长之后再删除

ALTER TABLE test14 MODIFY id INT UNSIGNED;
ALTER TABLE test14 DROP PRIMARY KEY ;

添加唯一

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [索引名称](字段名称,...)

ALTER TABLE user12 ADD UNIQUE(username);

ALTER TABLE user12 ADD CONSTRAINT symbol  UNIQUE KEY uni_card(card); 

ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test,test1);

删除唯一

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

ALTER TABLE user12 DROP INDEX username;

ALTER TABLE user12 DROP KEY uni_card;

ALTER TABLE user12 DROP KEY mulUni_test_test1;

修改表的存储引擎

ALTER TABLE tbl_name ENGINE=存储引擎名称

设置自增长的值

首先保证表中有字段是自增长的

ALTER TABLE tbl_name AUTO_INCREMNET=值

删除数据表

DROP TABLE [IF EXISTS] tbl_name[,tbl_name...]

DROP TABLE user12;

DROP TABLE IF EXISTS user12;

DROP TABLE IF EXISTS user11,user10,user9;

DROP TABLE IF EXISTS user123,user8,user7,user6;

数据的操作(DML)

插入数据

不指定具体的字段名

INSERT [INTO] tbl_name VALUES|VALUE(值...)

INSERT INTO user VALUES(1,'KING','KING','KING@QQ.COM',20);

INSERT user VALUE(2,'QUEEN','QUEEN','QUEEN@QQ.COM',30);

列出指定字段

INSERT [INTO] tbl_name(字段名称1,...) VALUES|VALUE(值1,...)

# 值和字段一一对应
INSERT user(username,password) VALUES('A','AAA');

INSERT user(username,id,password,email,age) VALUES('C',55,'CCC','CCC@QQ.COM',DEFAULT);

同时插入多条记录

INSERT [INTO] tbl_name[(字段名称...)] VALUES(值...),(值...)...

INSERT user VALUES(6,'D','DDD','D@QQ.COM',35),
(8,'E','EEE','E@QQ.COM',9),
(18,'F','FFF','F@QQ.COM',32);

通过SET形式插入记录

INSERT [INTO] tbl_name SET 字段名称=值,...

INSERT INTO user SET id=98,username='test',password='this is a test',email='123@qq.com',
age=48;

INSERT user SET username='maizi',password='maizixueyuan' ;

将查询结果插入到表中

INSERT [INTO] tbl_name[(字段名称,...)] SELECT 字段名称 FROM tbl_name [WHERE 条件]

INSERT testUser SELECT id,username FROM user;

字段数目不匹配

# 错误
INSERT testUser SELECT * FROM user;
# 正确
INSERT testUser(username) SELECT username FROM user;

更新数据

UPDATE tbl_name SET 字段名称=值,... [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

无where，表中的所有记录都会更新

将用户表中所有的用户年龄更新15

UPDATE user SET age=5;

# 将第一个记录的password，email，age改变
UPDATE user SET password='king123',email='123@qq.com',age=99
WHERE id=1;

UPDATE user SET age=age-5 WHERE id>=3;

UPDATE user SET age=DEFAULT WHERE username='A';

删除数据

DELETE FROM tbl_name [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

# 删除testUser表中的记录
DELETE FROM testUser ;
# 删除user表中id为1的用户
DELETE FROM user WHERE id=1;

彻底清空数据表

TRUNCATE [TABLE] tbl_name

auto increment值不重置

TRUNCATE TABLE user;

auto increment值重置

查询数据操作(DQL)

查询记录

SELECT select_expr [, select_expr ...] 
     [
         FROM table_references 
         [WHERE 条件]
         [GROUP BY {col_name | position}  [ASC | DESC], ... 分组]
         [HAVING 条件 对分组结果进行二次筛选]
         [ORDER BY {col_name | position} [ASC | DESC], ...排序]
         [LIMIT 限制显示条数]
     ]

查询表达式

每一个表达式表示想要的一列，必须至少有一列，多个列之间以逗号分隔

# 查询管理员编号和名称
SELECT id,username FROM cms_admin;

SELECT username,id,role FROM cms_admin;

*表示所有列,tbl_name.*可以表示命名表的所有列

SELECT * FROM cms_admin;

SELECT cms_admin.* FROM cms_admin;

表来自于哪个数据库下db_name.tbl_name

SELECT id,username,role FROM cms.cms_admin;

SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;

字段来自于哪张表
表名.字段名

SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;

查询表达式可以使用[AS]alias_name为其赋予别名
- 给表名起别名

SELECT id,username FROM cms_admin AS a;

SELECT id,username FROM cms_admin a;

SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;

- 给字段起别名

SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;

SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a; 

SELECT id AS proId,proId AS id,username FROM cms_user;

SELECT 1,2,3,4,5,id,username FROM cms_user;

WHERE 条件

在这里插入图片描述

比较

查询编号为1的用户

SELECT id,username,email FROM cms_user WHERE id=1;

SELECT id,username,email FROM cms_user WHERE username='king';

查询编号不为1的用户

SELECT  * FROM cms_user WHERE id!=1;

SELECT  * FROM cms_user WHERE id<>1;

添加age字段

ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;

INSERT cms_user(username,password,regTime,proId,age)

VALUES('test1','test1',1419811708,1,NULL);

是否为控制

查询表中记录age值为NULL

# 找不出来
SELECT * FROM cms_user WHERE age=NULL;
# 正确
SELECT * FROM cms_user WHERE age<=>NULL;

SELECT * FROM cms_user WHERE age<=>18;

IS NULL 或者IS NOT NULL

SELECT * FROM cms_user WHERE age IS NULL;

指定范围

BETWEEN … AND …

查询编号在3~10之间的用户

SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;

指定集合

IN
查询编号为1，3，5，7，9，11，13，100

SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100,1000);

查询proId为1 和3的用户

SELECT * FROM cms_user WHERE proId IN(1,3);

查询用户名为king,queen,张三，章子怡的记录

SELECT * FROM cms_user WHERE username IN('king','queen','张三','章子怡');
# 查询忽略大小写的区别
SELECT * FROM cms_user WHERE username IN('KinG','QUEEN','张三','章子怡');

匹配字符 - 模糊查询

%:代表0个一个或者多个任意字符
查询姓张的用户

SELECT * FROM cms_user WHERE username LIKE '张%';

查询用户名中包含in的用户

SELECT * FROM cms_user WHERE username LIKE '%in%';

_:代表1个任意字符
查询用户名为3位的用户

SELECT * FROM cms_user WHERE username LIKE '___';

用户名_i%

SELECT * FROM cms_user WHERE username LIKE '_I%';

SELECT * FROM cms_user WHERE username NOT LIKE '_I%';

查询多个条件

查询用户名为king并且密码为king的用户

SELECT * FROM cms_user WHERE username='king' AND password='king';

查询编号大于等于3的变量年龄不为NULL的用户

SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL;

查询编号大于等于3的变量年龄不为NULL的用户并且proId为的3

SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL AND proId=3;

查询编号在5~10的用户并且用户名为4位的用户

SELECT * FROM cms_user WHERE id BETWEEN 5 AND 10 AND username LIKE '____';

查询用户名以张开始或者用户所在身份为2,4的记录

SELECT * FROM cms_user WHERE username LIKE '张%' OR proId IN(2,4);

GROUP BY查询结果分组

按照用户所属身份分组proId

SELECT * FROM cms_user GROUP BY proId;

向用户表中添加性别字段

ALTER TABLE cms_user ADD sex ENUM('男','女','保密');

UPDATE cms_user SET sex='男' WHERE id IN(1,3,5,7,9);

UPDATE cms_user SET sex='女' WHERE id IN(2,4,6,8,10);

UPDATE cms_user SET sex='女' WHERE id IN(2,4,6,8,10);

UPDATE cms_user SET sex='保密' WHERE id IN(12,11);

按照用户性别分组

SELECT * FROM cms_user GROUP BY sex;

按照字段位置分组

SELECT * FROM cms_user GROUP BY 7;

按照多个字段分组

SELECT * FROM cms_user GROUP BY sex,proId;

查询编号大于等于5的用户按照sex分组

SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;

配合GROUP_CONCAT()得到分组详情

查询id,sex,用户名详情按照性别分组

SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;

查询id,sex,用户名详情按照性别分组

SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;

查询proId，性别详情，注册时间详情，用户名详情安装proId

SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime)
FROM cms_user GROUP BY proId;

配合聚合函数

查询编号,sex,用户名详情以及组中总人数按照sex分组

SELECT id,sex,GROUP_CONCAT(username)AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;

统计表中所有记录

SELECT COUNT(*) AS totalUsers FROM cms_user;
SELECT COUNT(id) AS totalUsers FROM cms_user;

COUNT(字段)不统计NULL值

平均年龄，以及年龄总和按照性别分组

SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex;

配合WITH ROLLUP记录上面所有记录的总和

SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM cms_user
GROUP BY sex WITH ROLLUP;

HAVING子句

通过HAVING子句对分组结果进行二次筛选
只能使用在分组 GROUP BY之后

查询性别sex,用户名详情,组中总人数，最大年龄，年龄总和,根据性别分组

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
GROUP BY sex;

查询组中人数大于2并且最大年龄大于60的

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;

查询编号大于等于2的用户

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
WHERE id>=2
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;