mysql--表数据的操作

插入数据

--测试插入数据
INSERT 表名 VALUES|VALUE(值...)--当没有指定插入顺序的时候按照表的原来的顺序进行插入
INSERT 表名(需要赋值的字段名称) VALUES|VALUE(值...)--这里的值必须和前面需要赋值的字段对应
INSERT 表名(需要赋值的字段名称，这里可以用可以不用) VALUES(值...),(值...),(值)...;--这个是一次插入多条记录
INSERT 表名 SET 字段名称=值，字段名称=值.....(列出需要赋值的字段)
INSERT 表名[字段名称] SELECT 字段名称 FROM 表名 [WHERE 条件]

CREATE TABLE IF NOT EXISTS user8(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username CHAR(20) NOT NULL UNIQUE,
password VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '592681026@qq.com',
age TINYINT UNSIGNED DEFAULT 18
);

INSERT user8 VALUES(1,'KNIG','KING',DEFAULT,20);
INSERT user8(username,password) VALUES('FUZHIQIANG','2222');
--一次插入多条记录
INSERT user8 VALUES(55,'dd','ddddd',DEFAULT,22),
(3,'aaaaa','ssss',DEFAULT,23),
(4,'dddff','gg',DEFAULT,24);


--第五个插入方法
CREATE TABLE IF NOT EXISTS user9(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username CHAR(50) NOT NULL UNIQUE
);

--将查询结果插入到表中(将user8的查询结果插入到user9中)
INSERT user9 SELECT id,username FROM user8;--数据必须符合要求

更新和删除数据

--测试更新和删除操作
UPDATE 表名 SET 字段名称=值,... [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]
DELETE FROM 表名 [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]--这个删除数据是不会让AUTO_INCREMENT的值改变
TRUNCATE [TABLE] 表名--车顶清空数据表--这个可以删除AUTO_INCREMENT

--将用户表中的所有用户的年龄更新为15
UPDATE user8 SET age=5;--如果不加入WHERE条件加入的话，所有数据都会被更新

--将第一个记录的password email age更新
UPDATE user8 SET password='123',email='sss@qq.com',age=5
WHERE id=1;

--值用表达式表示
UPDATE user8 SET age=age-5 WHERE id>=3;

--删除表中的记录
DELETE FROM user9;

--删除表中id=1的用户。
DELETE FROM user8 WHERE id=1;

查询表达式　　

　　*表示所有的列，tbl_name.*可以表示命名表的所有列，查询表达式可以使用[AS]alias_name 为其赋予别名

--查询语句
SELECT * FROM cms_admin;--单表操作
SELECT cms_admin.* FROM cms_admin;--多表操作

--查询管理员的编号和名称
SELECT id,username FROM cms_admin;--查询某些字段值
SELECT username,id,role 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 id,username FROM cms_admin AS a;--表的别名为a，AS可以省略
SELECT a.id,a.username FROM cms_admin a;

--字段别名
SELECT id AS '编号',username AS '用户名' FROM cms_admin a;--这里字段的名字就改变了，显示的在表中的字段名称就是这个别名（暂时是这个显示）
SELECT a.id AS i,a.username AS u FROM cms_admin AS a;

　　待条件查询（WHERE，比较运算符）

--where条件  比较运算符
--查询编号大于等于1的用户
SELECT id,username,email FROM cms_user WHERE id>=1;--符号操作，小于，大于，不等于

SELECT * FROM cms_user WHERE age<=>NULL;--这个符号和=差不多 ，它多了一个用途就是用来检测是否为NULL

--IS NULL或者  IS NOT NULL
SELECT *FROM cms_user WHERE age IS NULL;--检测是否为NULL

--范围条件BETWEEN AND
--查询编号不在3-10之间的用户
SELECT *FROM cms_user WHERE id NOT BETWEEN 3 AND 10;--指定范围
--查询编好为1,3,4,5,6,7,8 IN(字段值的一个集合)，NOT IN 相反
SELECT *FROM cms_user WHERE id IN(1,3,4,5,6,7,8);--指定一个集合的查询，不区分大小写

　　模糊查询

--模糊查询  LIKE  NOT LIKE
--%；代表0个或1个或者多个任意字符
--_:代表一个任意字符
--查询姓张的用户
SELECT * FROM cms_user WHERE username LIKE '张%';

--查询用户名中包含in的用户
SELECT * FROM cms_user WHERE username LIKE '%ni%';

--查询所有的
SELECT * FROM cms_user WHERE username LIKE '%';

--查询用户名为3位的记录
SELECT * FROM cms_user WHERE username LIKE '___';

--查询第一位不知道，之后是i，后面的是什么不知道，
SELECT * FROM cms_user WHERE username LIKE '_i%';

SELECT * FROM cms_user WHERE username LIKE 'king';--这里相当于等号

　　逻辑运算符(AND,OR)

--查询用户名为king，密码为king
SELECT * FROM cms_user WHERE username='king' AND password='king';

--查询编号大于等于3的变量年龄不为NULL的又用户
SELECT * FROM cms_user WHERE id>=3 AND age <=>NULL;

　　分组查询

--分组查询   GROUP BY
--按照用户省份来分组
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(12,11);

--按照性别字段来分组
SELECT * FROM cms_user GROUP BY sex;--分组的是打印出来的是改组的第一个字段

--按照多个字段分组  性别，年龄，
SELECT * FROM cms_user GROUP BY sex,proId;--先按照性别分组，再按照省份

--WHERE 条件  对c查询结果进行分组
SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;

　　聚合函数查询

--分组查询配合聚合函数   
--查询编号性别用户名详情，按照性别分组   GROUP-CONCAT(字段)：得到某个分组的详情
SELECT id,sex,GROUP_CONCAT(username ) FROM cms_user GROUP BY sex;--得到分组详情（GROUP_CONCAT(字段)：得到某个字段的分组详情）

--查询proid，性别详情，注册时间详情，用户名详情，按照proid排分组
SELECT proId,GROUP_CONCAT(sex),GROUP_CONCAT(regTime),GROUP_CONCAT(username) FROM cms_user GROUP BY proId;

--聚合函数 COUNT(),MAX(),MIN(),AVG(),SUM();
--COUNT() 查询编号性别用户名详情以及组中总人数按照性别分组
SELECT id,sex,GROUP_CONCAT(username) AS users ,COUNT(*) AS Totlenum FROM cms_user GROUP BY sex;


--统计表中的所有记录
SELECT COUNT(*) FROM cms_user ;--统计这个表中有多少条记录(有多少个id)
SELECT COUNT(id) FROM cms_user ;

--COUNT(字段)  这种形式不会统计NULL的值。  COUNT(*) 统计所有，如果分组的话，就是统计分组中的总的

--查询编号性别用户命详情组中最大年龄，最小年龄，平均年龄，年龄总和。
SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS Totalsum, MAX(id),MIN(id),AVG(id),SUM(id) FROM cms_user GROUP BY sex;

--WITH ROLLUP 统计记录值的总和

　　having语句对分组结果进行二次筛选（只能配合分组GROUP BY使用）

--查询性别sex ，用户命详情，组中总人数，最大年龄，年龄总和按照性别分组
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS Totalsum,
MAX(id),
SUM(id) 
FROM cms_user 
GROUP BY sex
HAVING COUNT(*)>2;--二次筛选，组中人数大于二的

--查询组中人数大于2，并且组中最大id大于10
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS Totalsum,
MAX(id),
SUM(id) 
FROM cms_user 
WHERE id>=2
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(id)>10;

　　ORDER BY 对查询结果进行排序
- 　　默认的是升序

　　通过LIMIT 限制查询结果显示条数

　　LIMIT 显示条数

　　LIMIT 偏移量显示条数

--查询表中前三条记录
SELECT * FROM cms_user LIMIT 3;

SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;
--查询表中的第一条记录
SELECT * FROM cms_user LIMIT 1;
SELECT * FROM cms_user LIMIT 0,1;--第一个参数是偏移量，第二个参数是显示的总个数

　　查询语句总的规范

SELECT id,sex,age,GROUP_CONCAT(username),--详情函数 GROUP_CONCAT()
COUNT(*) AS Totalnumber,
MAX(id),
MIN(id),
AVG(id),
SUM(id)
FROM cms_user
GROUP BY sex--分组
HAVING COUTN(*)>=2--二次分组，总人数大于二，搭配GROUP BY 使用
ORDER BY id DESC--按照id降序排序
LIMIT 0,2;