# 创建数据库
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()不走索引