sql归纳与优化

# 创建数据库
CREATE DATABASE TEST02 CHARACTER SET UTF8;

# 查看所有数据库
SHOW DATABASES;

# 查看某一特定数据库
SHOW CREATE DATABASE test01;

 
# 删除某一数据库
DROP DATABASE test02;

# 查看正在使用的数据库
SELECT DATABASE();

# 切换数据库
USE pinyougou;

# 创建表
CREATE TABLE STUDENT(
    ID INT(10) PRIMARY KEY AUTO_INCREMENT,
    S_NAME VARCHAR(20) NOT NULL,
    SCHOOL_NAME VARCHAR(20),
    STU_AGE INT(3), 
    BIRTHDAY DATE 
)

# 查看数据库中的所有表
SHOW TABLES;

# 查看表结构
DESC student;

# 删除表
DROP TABLE tmp01;

# 修改表结构,增加表字段
ALTER TABLE student ADD `GRADE` VARCHAR(20) NOT NULL;

# 修改表结构，修改表字段属性
ALTER TABLE student MODIFY `GRADE` VARCHAR(30); 

# 修改表结构，修改表字段名称
ALTER TABLE student CHANGE `GRADE` STU_GRADE VARCHAR(50);

# 修改表结构，删除表字段
ALTER TABLE student DROP `TMP`;

# 修改表名
RENAME TABLE students TO student;

# 修改表的字符集
ALTER TABLE student CHARACTER SET gbk;

# 表中插入数据
INSERT INTO student (S_NAME,SCHOOL_NAME,STU_AGE,BIRTHDAY,STU_HOME,STU_GRADE) VALUES
('张三','北京大学',25,'2019-01-08','唐镇','二年级'),
('李四','南京大学',25,'2019-01-08','川沙','三年级');

# 更新语句
UPDATE student SET S_NAME='王五',SCHOOL_NAME='安徽大学' WHERE ID=2;

# 删除语句
DELETE FROM student WHERE ID=1;

SELECT *FROM  student

# 查询所有
SELECT * FROM student;

# 查询某些字段
SELECT stu_name,stu_age FROM student;

# 查询字段设置别名
SELECT stu_name AS NAME,stu_age AS age FROM student;
    
# 查询表设置别名
SELECT * FROM student AS stu WHERE stu.stu_name='张三';

# 查询去除重复（所有列相同视为重复）
SELECT DISTINCT * FROM student;

# 查询针对某一列去除重复
SELECT DISTINCT stu_name FROM student;

# 根据某几列去重查询
SELECT DISTINCT stu_name,stu_age FROM student;

# 查询列加10
SELECT id,stu_name,stu_school,stu_sex,stu_age+10 FROM student;

# 条件查询<,>,=,!=,<=,>=
SELECT * FROM  student WHERE stu_age <,>,=,!=,<=,>= ?;

# 条件查询BETWEEN
SELECT * FROM student WHERE stu_age BETWEEN 10 AND 25;

# 条件查询OR
SELECT * FROM student WHERE stu_age=25 OR stu_age=26;

# 条件查询IN
SELECT * FROM student WHERE stu_age IN(25,26);

# 条件查询LIKE
SELECT * FROM student WHERE stu_school LIKE '南_%';
注意：_代表一个模糊字符，%代表满足所有模糊条件；

# 条件查询是IS NULL，IS NOT NULL
SELECT * FROM student WHERE stu_school IS NULL;
SELECT * FROM student WHERE stu_school IS NOT NULL;

# 查询排序ORDER BY
SELECT * FROM student ORDER BY  stu_age DESC;
注意：ASC:升序(默认),DESC:降序

# 查询排序ORDER BY根据stu_age升序，根据id降序
SELECT * FROM student ORDER BY stu_age ASC, id DESC;

# 查询所有
SELECT * FROM student;

# 查询某些字段
SELECT stu_name,stu_age FROM student;

# 查询字段设置别名
SELECT stu_name AS NAME,stu_age AS age FROM student;
    
# 查询表设置别名
SELECT * FROM student AS stu WHERE stu.stu_name='张三';

# 查询去除重复（所有列相同视为重复）
SELECT DISTINCT * FROM student;

# 查询针对某一列去除重复
SELECT DISTINCT stu_name FROM student;

# 根据某几列去重查询
SELECT DISTINCT stu_name,stu_age FROM student;

# 查询列加10
SELECT id,stu_name,stu_school,stu_sex,stu_age+10 FROM student;

# 条件查询<,>,=,!=,<=,>=
SELECT * FROM  student WHERE stu_age <,>,=,!=,<=,>= ?;

# 条件查询BETWEEN
SELECT * FROM student WHERE stu_age BETWEEN 10 AND 25;

# 条件查询OR
SELECT * FROM student WHERE stu_age=25 OR stu_age=26;

# 条件查询IN
SELECT * FROM student WHERE stu_age IN(25,26);

# 条件查询LIKE
SELECT * FROM student WHERE stu_school LIKE '南_%';
注意：_代表一个模糊字符，%代表满足所有模糊条件；

# 条件查询是IS NULL，IS NOT NULL
SELECT * FROM student WHERE stu_school IS NULL;
SELECT * FROM student WHERE stu_school IS NOT NULL;

# 查询排序ORDER BY
SELECT * FROM student ORDER BY  stu_age DESC;
注意：ASC:升序(默认),DESC:降序

# 查询排序ORDER BY根据stu_age升序，根据id降序
SELECT * FROM student ORDER BY stu_age ASC, id DESC;

# 去除重复并且按照id降序排序
SELECT DISTINCT stu_age FROM student ORDER BY stu_age DESC;

# 聚合函数查询COUNT、SUM、MAX、MIN、AVG
# 查询总条数
SELECT COUNT(*) FROM student;

# 查询所有人的年龄总和
SELECT SUM(stu_age) AS total_age FROM student;

# 查询所有年龄<=25的人员总数
SELECT COUNT(*) FROM student WHERE stu_age<=25;

# 查询所有北京大学的人的所有年龄的平均值
SELECT AVG(stu_age) FROM student WHERE stu_school='北京大学';

# 查询北京大学中年龄最大的年龄
SELECT MAX(stu_age) FROM student WHERE stu_school='北京大学';

# 查询北京大学的年龄最小的年龄
SELECT MIN(stu_age) FROM student WHERE stu_school='北京大学';

# 查询北京大学年龄总和
SELECT SUM(stu_age) FROM student WHERE stu_school='北京大学';

# 查询北京大学年龄最大值和年龄最小值
SELECT MAX(stu_age),MIN(stu_age) FROM student WHERE stu_school='北京大学';
 
# 查询id为0,5,6,7的年龄平均值
SELECT AVG(stu_age) FROM student WHERE id IN(0,5,6,7);

SELECT * FROM scores
# 查询每门课程的平均分
SELECT AVG(score),sname FROM scores GROUP BY sname;

# 统计每门课程的平均分，且只显示平均分>70分的信息；
SELECT AVG(score),sname FROM scores GROUP BY sname HAVING AVG(score)>70;

# 统计各个学校的人数
SELECT COUNT(*),stu_school FROM student GROUP BY stu_school; 

# 统计各个学校的人数并且学生人数>1
SELECT COUNT(*),stu_school FROM student GROUP BY stu_school HAVING COUNT(*)>1;

# 数据库备份执行语句
mysql dump -uroot -proot test01>D:\aa.sql;

# 添加主键
ALTER TABLE 从表 ADD [CONSTRAINT 外键名称] FOREIGN KEY 从表名(从表外键字段名) REFERENCES 主表 名(主表的主键); 

# 删除外键约束的 
ALTER TABLE user_role DROP FOREIGN KEY fore_user_id;

# 删除主键
ALTER TABLE student DROP PRIMARY KEY id;

#创建分类表 
CREATE TABLE category( 
cid VARCHAR(32) PRIMARY KEY , 
cname VARCHAR(100) 
);

# 商品表 
CREATE TABLE `products` ( 
`pid` VARCHAR(32) PRIMARY KEY , 
`name` VARCHAR(40) , `price` DOUBLE );

#添加外键字段 ，一般建议“_fk”结尾
ALTER TABLE products ADD CONSTRAINT product_fk FOREIGN KEY (category_id) REFERENCES category (cid);

#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');

#2 向商品表添加普通数据,没有外键数据，默认为null 
INSERT INTO products (pid,NAME) VALUES('p001','商品名称');

#3 向商品表添加普通数据，含有外键信息(category表中存在这条数据) 
INSERT INTO products (pid ,NAME ,category_id) VALUES('p002','商品名称2','c001');

#4 向商品表添加普通数据，含有外键信息(category表中不存在这条数据) #  失败,异常 
INSERT INTO products (pid ,NAME ,category_id) VALUES('p003','商品名称2','c001');

#订单表和订单项表的主外键关系
ALTER TABLE `orderitem` ADD CONSTRAINT orderitem_orders_fk FOREIGN KEY (oid) REFERENCES orders(oid);

#商品表和订单项表的主外键关系
ALTER TABLE `orderitem` ADD CONSTRAINT orderitem_product_fk FOREIGN KEY (pid) REFERENCES products(pid);

#联合主键（可省略） 
ALTER TABLE `orderitem` ADD PRIMARY KEY (oid,pid);

# 创建users表
CREATE TABLE users(
    uid INT(32) PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(18),
    PASSWORD VARCHAR(8)
)

# 创建role表
CREATE TABLE role(
    rid INT(32) PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(32) 
)

# 创建user_role表
CREATE TABLE user_role(
    user_id INT(32),
    role_id INT(32)
)

# user_role添加外键
ALTER TABLE user_role ADD CONSTRAINT fore_user_id FOREIGN KEY user_role(user_id) REFERENCES users(uid);

# user_role添加外键
ALTER TABLE user_role ADD CONSTRAINT fore_role_id FOREIGN KEY user_role(role_id) REFERENCES role(rid);

# 查询当前链接数据库的使用情况
SHOW STATUS LIKE 'Com_______';

# 查询整个数据库的使用情况
SHOW GLOBAL STATUS LIKE 'Com_______';

#--------------------------------------------------------------------------------------------------------------------------------

# 创建索引
CREATE INDEX index_name ON student(stu_name);

# 主键也是索引，创建索引并合理利用索引才能提高效率;

# 1避免索引失效

# 1.1全值索引匹配查询，查询字段被索引字段全部覆盖，查询效率最高;

# 1.2最左前缀法则，如果索引为多列，要遵守最左缀法则，指的查询从索引的最左前列开始，且不跳过索引中的列；

# 1.3范围查询右边的列，不能使用索引
# stu_school索引不生效
SELECT * FROM student WHERE stu_name='张三' AND stu_age>20 AND stu_school='北京大学';

# 1.4不要再索引列上进行运算操作，否则索引失效;
#以下stu_name虽然是索隐列，但是使用函数运算所以索引失效;
SELECT * FROM student WHERE SUBSTRING(stu_name,3,2) = '张三';

# 1.5字符串不加单引号，索引会失效;
SELECT * FROM student WHERE stu_name='张三';

# 1.6.尽量使用覆盖索引，避免使用select *，
# 只访问索引的查询(索引列完全包含查询列)，select *中包含索引列不包含的字段，查询索引之后还要进行回表查询；

# 1.7.用or分割开的条件，如果or前的条件中有索引，而后面的列中没有索引，整个索引都会失效。
# 不走索引的案例
SELECT * FROM student WHERE stu_name='张三' OR stu_school='北京大学';
#走索引的案例
SELECT * FROM student WHERE stu_name='张三' AND stu_school='北京大学';

# 1.8.以%开头的like模糊查询索引失效，%结尾的模糊查询不影响索引;
# 索引失效
SELECT * FROM student WHERE stu_name LIKE '%张三%';
SELECT stu_name(索引列),stu_school(索引列)，stu_age(非索引列) FROM student WHERE stu_name LIKE '%张三%';
# 解决办法：通过覆盖索引解决;
SELECT stu_name(索引列),stu_school(索引列) FROM student WHERE stu_name LIKE '%张三%';

# 1.9.执行中如果索引在表中对应的数据很少，sql执行计划可能不会执行索引，直接进行全表扫描查询，mysql评估索引比全表慢则不适用索引，进行权标查询;

# 查询表中的索引
SHOW INDEX FROM student;

# 1.11 in()走索引，not in()不走索引