MySQL常用命令2

SQL-DQL语句

-- SQL-DQL语句
-- 比较运算符
-- 查询math分数大于80分的学生
SELECT * FROM stu3 WHERE math>80;

-- 查询english分数小于或等于80分的学生
SELECT * FROM stu3 WHERE english<=80;

-- 查询age等于20岁的学生
SELECT * FROM stu3 WHERE age=20;

-- 查询age不等于20岁的学生
SELECT * FROM stu3 WHERE age!=20;
SELECT * FROM stu3 WHERE age<>20;

-- 逻辑运算符
-- 查询age大于35且性别为男的学生(两个条件同时满足)
SELECT * FROM stu3 WHERE  age>35 AND sex='男';

-- 查询age大于35或性别为男的学生(两个条件其中一个满足)
SELECT * FROM stu3 WHERE age>35 OR sex='男';

-- 查询id是1或3或5的学生
SELECT * FROM stu3 WHERE id=1 OR id=3 OR id=5;

-- in关键字
-- 查询id是1或3或5的学生
SELECT * FROM stu3 WHERE id IN (1,3,5);

-- 查询id不是1或3或5的学生
SELECT * FROM stu3 WHERE id NOT IN (1,3,5);

-- 范围
-- 查询english成绩大于等于75，且小于等于90的学生
SELECT * FROM stu3 WHERE english>=75 AND english<=90;
SELECT * FROM stu3 WHERE english BETWEEN 75 AND 90;

-- 模糊查询 like 
-- _ : 模糊一位
-- % : 模糊所有，0个或多个
-- 查询姓马的学生
SELECT * FROM stu3 WHERE NAME LIKE '马%';
-- 查询姓名中包含'德'字的学生
SELECT * FROM stu3 WHERE NAME LIKE '%德%';
-- 查询姓马，且姓名有三个字的学生
SELECT * FROM stu3 WHERE NAME LIKE '马__';

-- 排序
-- 查询所有数据,使用年龄降序排序
SELECT * FROM stu3 ORDER BY age DESC;

-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
SELECT * FROM stu3 ORDER BY age DESC, math DESC;

-- 查询英语学生总数，如果english为null,则把它设为0，count不记录null值
SELECT COUNT(IFNULL(english,0)) FROM stu3;

-- 查询年龄大于40的总数
SELECT COUNT(*) FROM stu3 WHERE age>40;

-- 查询数学成绩总分
SELECT SUM(math) FROM stu3;

-- 查询数学成绩平均分，decimal(5,3)，3小数位数
SELECT AVG(math) FROM stu3;  
select cast(avg(math)  as decimal(5,3)) from stu3;

-- 查询数学成绩最高分
SELECT MAX(math) FROM stu3;

-- 查询数学成绩最低分
SELECT MIN(math) FROM stu3;

-- 分组
-- 按性别分组
SELECT sex FROM stu3 GROUP BY sex;

-- 查询男女各多少人
SELECT sex, COUNT(*) FROM stu3 GROUP BY sex;

-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
SELECT sex, COUNT(*) FROM stu3 WHERE age > 25 GROUP BY sex;

-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
SELECT sex, COUNT(*) FROM stu3 WHERE age > 25 GROUP BY sex HAVING COUNT(*) >2;

-- limit语句
-- 查询学生表中数据，从第三条开始显示，显示6条
SELECT * FROM stu3 LIMIT 2,6;

-- 每页显示5条
-- 第一页： LIMIT 0,5;	跳过0条，显示5条
-- 第二页： LIMIT 5,5;  跳过5条，显示5条
-- 第三页： LIMIT 10,5; 跳过10条，显示5条
SELECT * FROM stu3 LIMIT 0,5;
SELECT * FROM stu3 LIMIT 5,5;
SELECT * FROM stu3 LIMIT 10,5;

-- 别名解析
-- 给表product取个小名p
select pname,price from product as p;
-- 或者：
select p.pname,p.price from product as p;

数据库约束

约束的类型

-- 创建表用户表, 包含字段(id, name)将id做为主键
-- 主键约束
-- 方式1: 建表时在字段的约束区添加主键约束
CREATE TABLE user1(
	id INT PRIMARY KEY, # 在字段的约束区添加主键约束
	`name` VARCHAR(20)
);

-- 添加数据信息
INSERT INTO user1 VALUES(1,'tom'); -- 正常插入
INSERT INTO user1 VALUES(NULL,'tom'); -- 插入异常(主键不可为空): Column 'id' cannot be null
INSERT INTO user1 VALUES(1,'tom'); -- 插入异常(主键不可重复): Duplicate entry '1' for key 'PRIMARY'

-- 方式3: 创建完表后,通过修改表结构添加主键约束
CREATE TABLE user3(
	id INT, 
	`name` VARCHAR(20)
);
-- 修改表添加主键
ALTER TABLE user3 ADD PRIMARY KEY (id);


-- 创建表用户表, 包含字段(id, name) 将id和name做为联合主键
-- 联合主键,注意 : 一张表中只有一个主键 , 主键可以为多个字段 , 不过我们一般增加一个字段 id 来作为主键.
CREATE TABLE user4(
	id INT, 
	`name` VARCHAR(20),
	PRIMARY KEY (id,`name`)
);
-- 保证id和name字段不为空,然后联合判断唯一性
INSERT INTO user4 VALUES(1,'tom');	-- 正常插入
INSERT INTO user4 VALUES(2,"tom");	-- 正常插入
INSERT INTO user4 VALUES(1,"rose");	-- 正常插入
INSERT INTO user4 VALUES(2,"rose");	-- 正常插入
INSERT INTO user4 VALUES(1,"tom");	-- 插入异常:Duplicate entry '1-tom' for key 'PRIMARY'
INSERT INTO user4 VALUES(NULL,"jack"); -- 插入异常:Column 'id' cannot be null
INSERT INTO user4 VALUES(3,NULL); -- 插入异常:Column 'name' cannot be null

-- 删除主键
-- 删除user1表的主键
ALTER TABLE user1 DROP PRIMARY KEY;

-- 主键自增
-- 创建学生表user5, 包含字段(id, name)将id做为主键并自动增长
CREATE TABLE user5 (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
);

-- 主键默认从1开始自动增长
INSERT INTO user5 (name) VALUES ('唐僧');
INSERT INTO user5 (name) VALUES ('孙悟空');
INSERT INTO user5 VALUES (null,'猪八戒');
INSERT INTO user5 VALUES (null,'沙僧');

-- 唯一约束:在这张表中这个字段的值不能重复
-- 创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生
CREATE TABLE st7 (
	id INT,
	NAME VARCHAR(20) UNIQUE
);

-- 添加一个学生
INSERT INTO st7 VALUES (1, '貂蝉');
INSERT INTO st7 VALUES (2, '西施');
INSERT INTO st7 VALUES (3, '王昭君');
INSERT INTO st7 VALUES (4, '杨玉环');

-- 插入相同的名字出现name重复: Duplicate entry '貂蝉' for key 'name'
INSERT INTO st7 VALUES (5, '貂蝉');

-- 出现多个null的时候会怎样？因为null是没有值，所以不存在重复的问题
INSERT INTO st3 VALUES (5, NULL);
INSERT INTO st3 VALUES (6, NULL);


-- 非空约束
-- 创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL
CREATE TABLE st8 (
	id INT,
	NAME VARCHAR(20) NOT NULL,
	gender CHAR(2)
);

-- 默认值：往表中添加数据时,如果不指定这个字段的数据,就使用默认值
-- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州
CREATE TABLE st9 (
	id INT,
	NAME VARCHAR(20),
	address VARCHAR(50) DEFAULT '广州'
);

-- 添加一条记录,使用默认地址
INSERT INTO st9 (id, NAME) VALUES (1, '刘德华');

-- 添加一条记录,不使用默认地址
INSERT INTO st9 VALUES (2, '张学友', '香港');