MySQL-DAY02

1.自增长字段

一个表中只能有一个自增长字段，自增长字段一定是主键。

2.非空约束

NOT NULL

3.默认值

默认值一般与非空约束配合使用

4.唯一性约束

一个表中可以多个唯一，null 不算唯一

-- 测试自增长 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('Tom');
INSERT user5 VALUES(111,'King1');
INSERT user5 VALUES(NULL,'King2');
INSERT user5 VALUES(DEFAULT,'King3');

CREATE TABLE IF NOT EXISTS user6(
id SMALLINT KEY auto_increment, #自增长和主键配合使用
username VARCHAR(20)
)AUTO_INCREMENT=100;  #编号从100开始
INSERT user6(username) VALUES('Tom');
-- 修改自增长的值
ALTER TABLE user6 AUTO_INCREMENT =500;
INSERT user6(username) VALUES('Tom2');

-- 非空约束 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,age) VALUES('king','king',12);

-- 测试默认值 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(20) NOT NULL DEFAULT '北京',
sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
);
INSERT user8(username,password) VALUES('king','123');
INSERT user8 VALUES(2,'king1','123',20,'上海','女');
INSERT user8 VALUES(3,'king2','123',DEFAULT,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);


-- CREATE TABLE [IF NOT EXISTS] TAB_NAME(
-- 字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY| UNIQUE [KEY]] [AUTO_INCREMENT]
-- )ENGINE=INNODB CHARSET=UTF8 AUTO_INCERMENT=100;
-- 创建一个用户表
CREATE TABLE IF NOT EXISTS user10(
id SMALLINT UNSIGNED KEY auto_increment,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '123456789@qq.com',
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('男','女','保密') DEFAULT '保密',
addr VARCHAR(200) NOT NULL DEFAULT '北京',
salary FLOAT(6,2),
regTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'default,jpg'
);


-- 修改表名
-- 将user10重命名为user11
ALTER TABLE user10 RENAME TO user11;
ALTER TABLE user11 RENAME AS user10;
ALTER TABLE user10 RENAME user11;
RENAME TABLE user11 TO user10;


-- 添加card字段 char(18)
-- 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');
-- 删除test6字段
ALTER TABLE user10 DROP test6;
ALTER TABLE user10 DROP test1;
-- 一次删除多个字段
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;


-- 修改字段，将email字段修改为varchar(200)
-- ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]
ALTER TABLE user10 MODIFY email VARCHAR(200) NOT NULL DEFAULT '345678@qq.com'；
-- 将card字段移动到test字段之后
ALTER TABLE user10 MODIFY card CHAR(18) AFTER test;
-- 将test字段修改为 CHAR(32) NOT NULL DEFAULT '123' FIRST
ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST
-- 修改字段名称
-- 将test字段改为test1
ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';
ALTER TABLE user10 CHANGE test1 test VARCHAR(200) NOT NULL DEFAULT '234' AFTER username;
ALTER TABLE user10 CHANGE test test INT;


-- 添加默认值
-- ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值
CREATE TABLE IF NOT EXISTS user11(
id TINYINT UNSIGNED KEY auto_increment,
username VARCHAR(20) NOT NULL UNIQUE,
age TINYINT UNSIGNED
);
ALTER TABLE user11 ALTER age set DEFAULT 18;
ALTER TABLE user11 ADD email VARCHAR(50);
ALTER TABLE user11 ALTER email set DEFAULT '123@qq.com';
-- 删除默认值
ALTER TABLE user11 ALTER age DROP DEFAULT;
ALTER TABLE user11 ALTER email DROP DEFAULT;


-- 添加主键
-- ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY[index_type] (字段名称,...)
CREATE TABLE  IF NOT EXISTS test12(
id INT,
card CHAR(18),
username VARCHAR(20) NOT NULL
);
ALTER TABLE test12 ADD PRIMARY KEY(id);
-- 设置复合主键
CREATE TABLE  IF NOT EXISTS test13(
id INT,
card CHAR(18),
username VARCHAR(20) NOT NULL
);
ALTER TABLE test13 ADD PRIMARY KEY(id,card);
-- 删除user13的主键
ALTER TABLE test13 DROP PRIMARY KEY;

ALTER TABLE test13 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);
-- 只有一个字段，且该字段为主键并自增长时，需要先去掉自增长再去掉主键
CREATE TABLE  IF NOT EXISTS test14(
id INT UNSIGNED KEY auto_increment
);
ALTER TABLE test14 MODIFY id INT UNSIGNED;  #去掉自增长
ALTER TABLE test14 drop PRIMARY KEY;  #去掉主键

-- 添加唯一索引
-- ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [索引名称](字段名称,...)
CREATE TABLE IF NOT EXISTS user12(
id TINYINT UNSIGNED KEY auto_increment,
username VARCHAR(20) NOT NULL,
card CHAR(18) not NULL,
test VARCHAR(20) NOT NULL,
test1 CHAR(32) NOT NULL
);
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 mul_test_test1(test,test1);
-- 删除唯一索引
ALTER TABLE user12 DROP INDEX username;
ALTER TABLE user12 DROP KEY uni_card;
ALTER TABLE user12 DROP KEY mul_test_test1


-- 修改表达存储引擎为MyISAM
ALTER TABLE user12 ENGINE=MyISAM;
ALTER TABLE user12 ENGINE=INNODB;

5.DML操作

-- 数据表的DML操作
-- 修改自增长值
ALTER TABLE user12 auto_increment=100;



-- 删除数据表
DROP TABLE user12;


-- 测试插入记录 INSERT
CREATE TABLE IF NOT EXISTS user13(
id TINYINT UNSIGNED auto_increment KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '235@qq.com',
age TINYINT UNSIGNED DEFAULT 18
);
INSERT user13 VALUES(1,'king','king','king@qqq.com',20);
INSERT user13 VALUE(2,'king1','king1','king1@qqq.com',21);
INSERT user13(username,password) VALUES('BBB','B');
INSERT user13(username,id,password,email,age) VALUES('C',22,'ccc','ccc@qq.com',DEFAULT);
INSERT user13 VALUES(6,'d','ddd','d@qq.com',34),
(8,'f','fff','f@qq.com',25),
(23,'r','rrr','r@qq.com',32);


-- 测试插入记录 INSERT SET
INSERT INTO user13 set id =25,username='test',password='this is a test',email='ewq@qq.com',
age=48;
INSERT user13 SET username='ccds',password='test';

-- 测试使用查询结果插入表中
CREATE TABLE IF NOT EXISTS testuser(
id TINYINT UNSIGNED KEY auto_increment,
username VARCHAR(20) NOT NULL UNIQUE
);
INSERT testuser SELECT id,username from user13;
INSERT testuser(username) SELECT username FROM user13;

-- 以下命令会显示字段数目不匹配
INSERT testuser SELECT * FROM user13;


-- 更新表中的数据
-- 将用户表中的所有用户年龄更新为15
UPDATE user13 SET age=15;
UPDATE user13 SET age=20,email='12@qq.com';
-- 将第一条记录的password, email, age
UPDATE user13 SET `password`='king123',email='44@qq.com',age=99 WHERE id=1;
UPDATE user13 SET age=age-5 WHERE id>=4;
UPDATE user13 SET age=DEFAULT WHERE username='C';

-- 删除数据
-- 删除testuser表中的记录
DELETE FROM testuser;
-- 删除user13表中id=1的用户
DELETE FROM user13 WHERE id=1; #当设置自增长时，再次插入记录编号会从自增长值开始

-- 彻底清空数据表
TRUNCATE TABLE user13;  #使用 TRUNCATE 可以使自增长数值复原

6.DQL操作

where查询条件

聚合函数

-- 数据表中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 user13;
-- 表来自于哪个数据库下的db_name.tbl_name
SELECT id,username FROM maizi.user13;
-- 字段来自于哪张表
SELECT user13.id,user13.username FROM maizi.user13;
-- 给表名起别名
SELECT id,username FROM user13 as a; # AS 可以省略
SELECT a.id,a.username FROM user13 AS a;
-- 给字段起别名
SELECT id AS '编号',username AS '用户名',password AS '密码',email AS '邮箱',age AS '年龄' FROM user13; # AS 可以省略
SELECT a.id AS i,a.username AS u,a.email AS e,a.age AS g FROM user13 as a;
SELECT id AS '编号',username AS '用户名',age,password FROM user13;
SELECT 'ok',2,3,4,5,id,username FROM user13;


-- WHERE 条件
-- 查询编号为1的用户
SELECT id,username,email FROM user13 WHERE id=1; #当编号不存在时返回值为空集
SELECT id,username,email FROM user13 WHERE username='king';
SELECT id,username,email FROM user13 WHERE id>=4;
SELECT id,username,email FROM user13 WHERE id!=2;
SELECT id,username,email FROM user13 WHERE id <>1;

-- 添加 work_age 字段
ALTER TABLE user13 ADD work_age TINYINT UNSIGNED DEFAULT 2;
ALTER TABLE user13 DROP work_age;
INSERT user13 VALUES(50,'www','wwww','ww@qq.com',45,NULL);
-- 检测 NULL 时使用 <=>
SELECT * FROM user13 WHERE work_age <=> NULL;
SELECT * FROM user13 WHERE age <=> 18;
SELECT *FROM user13 WHERE work_age IS NULL;  #使用 <=> NULL 和 IS NULL 都可以检测 NULL ,也可以用 IS NOT NULL 检测不是空值
-- 查询编号在3-10之间的用户
SELECT * FROM user13 WHERE id BETWEEN 3 AND 10;
-- 查询编号在小于3大于10的用户
SELECT * FROM user13 WHERE id NOT BETWEEN 3 AND 10;
-- 查询编号为1,3,5,7,9,11,13,100
SELECT * FROM user13 WHERE id IN(1,3,5,7,9,11,13,100); 
-- 查询age为18,25的用户
SELECT * FROM user13 WHERE age in(18,25);
-- 查询用户名为 King,张三的记录
SELECT * FROM user13 WHERE username in('King','张三');  #在查询时会忽略大小写，把 IN 改为 NOT IN 可以进行取反

-- 模糊查询
-- %：代表0个1个或者多个任意字符
-- _: 代表1个任意字符
-- 查询用户名 K 开头的用户
SELECT * FROM user13 WHERE username LIKE '%K%';  # '%K%'包含 K 的用户，'K%'以 K 开头的用户，'%' 可视为没有条件
-- 查询用户名为3位的用户
SELECT * FROM user13 WHERE username LIKE '___';  # 用户名为几位，就写 几个 '_'
-- 用户名第二位为i的记录
SELECT * FROM user13 WHERE username LIKE '_i%'; 
-- 使用 LIKE 命令 精准查询 
SELECT * FROM user13 WHERE username LIKE 'king'; # 查询值中不包含 '_', '%' 时可以视为精准查询

-- 逻辑运算符
-- 查询用户名为 king 并且密码为 king 的用户
SELECT * FROM user13 WHERE username='king' AND `password`='king';
-- 查询编号 >=3 的变量且年龄不为 NULL work_age 为 NULL 的用户
SELECT * FROM user13 WHERE id >=3 AND age IS NOT NULL AND work_age <=> NULL;
-- 查询编号在5~10的用户，且用户名为四位
SELECT * FROM user13 WHERE id BETWEEN 5 AND 10 AND username LIKE '____';
-- 查询用户名以‘k’开头，或者用户年龄为18,25的用户
SELECT * FROM user13 WHERE username LIKE 'k%' OR age in(18,25);

-- 分组查询
-- 按照用户年龄分组
SELECT * FROM user13 GROUP BY age;

-- 向用户表中添加性别字段
ALTER TABLE user13 ADD sex ENUM('男','女','保密');
UPDATE user13 SET sex='男' WHERE id in(1,2,3);
UPDATE user13 SET sex='女' WHERE id in(8,6,22);
UPDATE user13 SET sex='保密' WHERE id in(23,50);
-- 按照性别分组
SELECT * FROM user13 GROUP BY sex;
-- 按照字段所处的位置分组
-- 按照年龄进行分组 （年龄在第5列）
SELECT * FROM user13 GROUP BY 5;
-- 按照多个字段分组
SELECT * FROM user13 GROUP BY sex,age;
-- 查询编号大于5的用户中按照性别分组
SELECT * FROM user13 WHERE id >=5 GROUP BY sex;


-- 查询 id,sex，用户名详情按照性别分组
SELECT id,sex,GROUP_CONCAT(username) FROM user13 GROUP BY sex;
-- 查询age,性别详情，用户名详情，按照年龄分组
SELECT age,GROUP_CONCAT(username),GROUP_CONCAT(sex) FROM user13 GROUP BY age;

-- 查询 id,sex,用户名详情以及组中总人数按照sex分组
SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalusers FROM user13 GROUP BY sex
-- 统计表中所有记录
SELECT COUNT(*) AS totalusers FROM user13;  # COUNT(*) 统计表中所有记录，包括NULL，使用COUNT()其他字段不统计null
-- 查询编号，性别，用户名详情，组中最大年龄，最小年龄，平均年龄，以及年龄总和,按照性别分组
-- WITH ROLLUP在末行增加一行，统计前述行内容，并再次根据行要求进行统计
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 user13
GROUP BY sex WITH ROLLUP;

-- HAVING 子句的二次筛选,要配合 GROUP BY 使用
-- 查询性别，用户名详情，组中总人数，最大年龄，年龄总和
-- 查询组中人数大于2的
SELECT sex,GROUP_CONCAT(username) as users,
COUNT(*) as totalusers,
MAX(age) as max_age,
SUM(age) as sum_age
FROM user13 GROUP BY sex
HAVING COUNT(*) >2 AND MAX(age)>30;

-- 查询编号大于等于2的用户
SELECT sex,GROUP_CONCAT(username) as users,
COUNT(*) as totalusers,
MAX(age) as max_age,
SUM(age) as sum_age
FROM user13
WHERE id >=2
GROUP BY sex
HAVING COUNT(*) >2 AND MAX(age)>30;

7.查询表信息

SHOW TABLES;  #显示现有的表
DESC user13;  #显示表结构的详细信息
SELECT * FROM user13;  #显示表内容
SHOW CREATE TABLE user12;   #显示创建表时的详细定义