07数据库复习01

1.数据库的概念

http://www.maiziedu.com/wiki/mysql/code/

2.Mysql5.6新特性

3.mysql存储引擎

4.修改表结构 

-- 创建数据库
CREATE DATABASE IF NOT EXISTS maizi DEFAULT CHARACTER SET 'UTF8';

USE maizi;

CREATE TABLE user10(
    uid INT  KEY AUTO_INCREMENT,
    uname VARCHAR(20),
    salary FLOAT(6,2) DEFAULT 1000 
);

DROP TABLE user10;
-- 重命名
ALTER TABLE user10 RENAME user11;

-- 添加字段
ALTER TABLE user11 ADD age INT;
ALTER TABLE user11 ADD sex VARCHAR(5) AFTER salary;

-- 一次添加多个字段
ALTER TABLE user11
ADD test1 VARCHAR(5),
ADD test2 FLOAT(6,2),
ADD test3 INT;

-- 删除字段

ALTER TABLE user11 DROP test1;

ALTER TABLE user11 
DROP sex,
DROP age;

-- 修改字段类型
DESC user11;

ALTER TABLE user11 MODIFY uname VARCHAR(10) NOT NULL;

ALTER TABLE user11 MODIFY test2 VARCHAR(10) NOT NULL FIRST;

-- 修改字段名称
ALTER TABLE user11 CHANGE test2 test1 VARCHAR(10);

DESC user11;
-- 删除默认字段
ALTER TABLE user11 ALTER salary DROP DEFAULT;

-- 添加默认字段
ALTER TABLE user11 ALTER uname SET DEFAULT 'hh';

DESC user11;
-- 添加主键
ALTER TABLE user11 ADD PRIMARY KEY(uid);
-- 删除主键
ALTER TABLE user11 DROP PRIMARY KEY;

CREATE TABLE user10(
    uid INT  KEY AUTO_INCREMENT,
    uname VARCHAR(20),
    salary FLOAT(6,2) DEFAULT 1000 
);
DESC user10;
-- 删除主键
ALTER TABLE user10 DROP PRIMARY KEY; -- 因为含有自增长，所以不能直接删除
-- 去除自增长
ALTER TABLE user10 MODIFY uid INT UNSIGNED;
-- 删除主键
ALTER TABLE user10 DROP PRIMARY KEY;

DESC user10;
-- 添加唯一约束
ALTER TABLE user10 ADD UNIQUE(uname);
-- 删除唯一约束
ALTER TABLE user10 DROP KEY uname;

5.查询操作

 

 

 

-- 分组查询
-- 按用户所属省份分组
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);

-- 按照性别分组
SELECT * FROM cms_user GROUP BY sex;

-- 按照多个字段分组
SELECT * FROM cms_user GROUP BY sex,proId;

-- 查询编号大于5的用户，按照sex分组
SELECT * FROM cms_user WHERE id >= 5 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) FROM cms_user GROUP BY proId;

ALTER TABLE cms_user ADD age INT;

UPDATE cms_user SET age = 11 WHERE id = 1;

UPDATE cms_user SET age = 12 WHERE id = 2;
UPDATE cms_user SET age = 14 WHERE id = 3;
UPDATE cms_user SET age = 11 WHERE id = 4;
UPDATE cms_user SET age = 34 WHERE id = 5;
UPDATE cms_user SET age = 32 WHERE id = 6;
UPDATE cms_user SET age = 15 WHERE id = 7;
UPDATE cms_user SET age = 17 WHERE id = 8;
UPDATE cms_user SET age = 19 WHERE id = 9;
UPDATE cms_user SET age = 20 WHERE id = 10;

-- 查询id，sex，username详情，组中总人数 按照sex分组
SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) FROM cms_user GROUP BY sex;

-- 统计表中所有的记录
SELECT COUNT(*) FROM cms_user;

-- count字段不统计null值
SELECT COUNT(age) FROM cms_user;

-- 查询id，sex，username详情，组中总人数，组中最大年龄，组中最小年龄，组中平均年龄，组中年龄和，按照性别分组
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 ;

-- 查询id，sex，username详情，组中总人数，组中最大年龄，组中最小年龄，组中平均年龄，组中年龄和，按照性别分组
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; -- with rollup 统计记录总和

-- 查询性别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的。
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 totalUsers > 2;

-- 对于上述的查询结果进行二次筛选，组中人数大于2的并且最大年龄大于33的。
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 totalUsers > 2 AND max_age > 33;

-- 按照id降序排列查询数据desc，默认为asc
SELECT * FROM cms_user ORDER BY id DESC;

SELECT id,age,sex,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age
FROM cms_user
WHERE id >= 2
GROUP BY sex
HAVING totalUsers>=2
ORDER BY age DESC,id ASC;

-- 实现随机记录
SELECT * FROM cms_user ORDER BY RAND();

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

-- 查询表中后5条记录
SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;

-- 带偏移量的显示条数
SELECT * FROM cms_user LIMIT 1,3; -- 前面一个数字是偏移量，后面一个是每页显示的条数