SQL语句大全（详解）

惊雷无声  论坛元老 | 2024-8-20 21:53:41 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1027|帖子 1027|积分 3081

前言


  本教程为常用SQL语句的汇总，我把他放到了码云上面，项目地址：https://gitee.com/love-code-bear/java/blob/master/SQL.md
1 DDL

1.1 显示所包罗的数据库

  1. SHOW DATABASES;
复制代码
1.2 创建数据库

  1. CREATE DATABASE db2;
  2. CREATE DATABASE IF NOT EXISTS db2;
复制代码
1.3 删除数据库

  1. DROP DATABASE db2;
  2. drop DATABASE IF EXISTS db2;
复制代码
– 检察当前所使用的数据库
  1. SELECT DATABASE();
复制代码
1.4 使用数据库

  1. use db1;
复制代码
1.4.1 创建表

  1. CREATE TABLE jd_user(
  2. id int,
  3. username VARCHAR(32),
  4. password VARCHAR(32)
  5. );
复制代码
1.4.2 检察表的结构

  1. DESC stu;
复制代码
1.4.3 检察当前数据库下的所有表

  1. USE DATABASE db1;
  2. SHOW TABLES;
复制代码
1.4.4 根本的增删改查

1.4.4.1 删除表

  1. DROP TABLE tb_user;
  2. DROP TABLE IF EXISTS tb_user;
复制代码
1.4.4.2 添加列

  1. ALTER TABLE jd_user ADD address VARCHAR(32);
复制代码
1.4.4.3 修改表名

  1. ALTER TABLE jd_user RENAME TO jd_user;
复制代码
1.4.4.4 修改数据类型

  1. ALTER TABLE jd_user MODIFY address CHAR(32);
  2. DESC jd_user;
复制代码
1.4.4.5 修改列名和数据类型

  1. ALTER TABLE jd_user CHANGE address location VARCHAR(64);
复制代码
1.4.5 查询所有数据
  1. SELECT * FROM jd_user;
  2. SELECT * FROM stu;
复制代码
2 DML

2.1 给指定列添加数据

2.1.1 修改中文列的编码格式(修改列名和数据类型)：

  1. alter table stu change name name varchar(255) character set utf8;
  2. INSERT INTO stu(id,name) VALUES(1,'张三');
复制代码
2.1.2 给所有列添加数据

  1. alter table stu change sex sex varchar(255) character set utf8;
  2. INSERT INTO stu(id,name,sex,birthday,score,email,tel,status) VALUES
  3. (2,'lisa','女','1999-11-11',98.00,'1@qq.com',1123,1);
复制代码
2.1.3 给所有列添加数据，列名的列表可以省略

  1. INSERT INTO stu VALUES(3,'小米','男','1998-10-17',93.00,'2@qq.com',1433,1);
复制代码
2.1.4 批量添加

  1. INSERT INTO stu VALUES
  2. (4,'huawei','男','1998-10-17',93.00,'2@qq.com',1433,1),
  3. (5,'荣耀','男','1998-10-17',93.00,'2@qq.com',1433,1),
  4. (6,'苹果','男','1998-10-17',93.00,'2@qq.com',1433,1);
复制代码
2.2 修改数据

2.2.1 将张三的性别改为男

  1. UPDATE stu SET sex = '男' WHERE name = '张三';
复制代码
2.2.2 将张三的生日改成2000-02-28，结果改成99.00

  1. UPDATE stu SET birthday = '2000-02-28',score = '99.00' WHERE name = '张三';
复制代码
2.2.3 假如update语句没有where条件，则表中的数据全部都被修改

2.3 删除数据

2.3.1 删除小米纪录

  1. DELETE FROM stu WHERE name = '小米';
复制代码
2.4 简朴的创建表，添加数据，查询数据

– 使用数据库
USE db1;
– 创建表
  1. CREATE TABLE stu1 (
  2. id int,
  3. name VARCHAR(32),
  4. age int,
  5. sex VARCHAR(4),
  6. address VARCHAR(64),
  7. math DOUBLE(5,2),
  8. english DOUBLE(5,2),
  9. hire_date DATE
  10. );
复制代码
– 插入数据
  1. ALTER TABLE stu1 CHANGE name name VARCHAR(32) character set utf8;
  2. ALTER TABLE stu1 CHANGE sex sex VARCHAR(4) character set utf8;
  3. ALTER TABLE stu1 CHANGE address address VARCHAR(64) character set utf8;
  4. INSERT INTO stu1(id,name,age,sex,address,math,english,hire_date)
  5. VALUES
  6. (1,'张一',25,'男','杭州',66.00,78.00,'1998-09-09'),
  7. (2,'张二',24,'女','北京',87.00,76.00,'1996-09-09'),
  8. (3,'张三',22,'男','郑州',94.00,65.00,'1997-09-09'),
  9. (4,'张四',23,'男','合肥',69.00,75.00,'1998-09-09'),
  10. (5,'张五',23,'女','无锡',76.00,79.00,'1999-09-09'),
  11. (6,'张六',24,'女','苏州',88.00,94.00,'1998-03-09'),
  12. (7,'张七',21,'男','南通',89.00,90.00,'1998-05-09'),
  13. (8,'张八',22,'男','南充',98.00,90.00,'1998-07-09');
复制代码
– 查询所有数据
  1. SELECT * FROM stu1;
  2. USE db1;
复制代码
3 DQL

3.1 根本查询

3.1.1 查询所有列的数据，列名的列表可以使用*代替

  1. SELECT *FROM stu1;
  2. SELECT `name`,age,sex,address,math,english,hire_date FROM stu1;
复制代码
3.1.2 查询name age 两列

  1. SELECT `name`,age FROM stu1;
复制代码
3.1.3 查询英语分数

  1. SELECT english FROM stu1;
复制代码
3.1.4 去除重复纪录

  1. SELECT DISTINCT english FROM stu1;
复制代码
3.1.5 查询时起别名 as

  1. SELECT name AS 姓名,math AS 数学,english AS 英语 FROM stu1;
复制代码
3.2 条件查询

3.2.1 查询年事大于23岁的学员信息

  1. SELECT * FROM stu1        WHERE age > 23;
复制代码
3.2.2 查询年事>=24岁的学员信息

  1. SELECT * FROM stu1 WHERE age >= 24;
复制代码
3.2.3 查询21<=年事<=23岁的学员信息

  1. SELECT * FROM stu1 WHERE age>=21 AND age <= 23;
  2. SELECT * FROM stu1 WHERE age BETWEEN 21 AND 23;
复制代码
3.2.4 查询入学时间在1997-05-09–1998-07-09之间的学员信息

  1. SELECT * FROM stu1 WHERE hire_date BETWEEN '1997-05-09' AND '1998-07-09';
复制代码
3.2.5 查询年事等于21岁的学员信息

  1. SELECT * FROM stu1 WHERE age = 21;
复制代码
3.2.6 查询年事等于21岁或者年事等于24岁或者年事等于25岁的学员信息

  1. SELECT * FROM stu1 WHERE age = 21 OR age = 24 OR age = 25;
  2. SELECT * FROM stu1 WHERE age IN(21,24,25);
复制代码
3.2.7 查询英语结果为空null的学员信息

  1. SELECT * FROM stu1 WHERE english IS NOT null;
复制代码
3.3 模糊查询

  3.3.1 查询姓’张’的学员信息

  1. SELECT * FROM stu1 WHERE `name` LIKE '张%';
  2. -- 查询第二个字是'三'的学员信息
  3. SELECT * FROM stu1 WHERE `name` LIKE '_三%';
  4. -- 查询名字中含有'四'的学员信息
  5. SELECT * FROM stu1 WHERE `name` LIKE '%四%';
复制代码
3.4 排序查询

  3.4.1 查询学生信息,按照年事升序排列

  1. SELECT * FROM stu1 ORDER BY age ASC;
复制代码
3.4.2 查询学生信息,按照数学结果降序排列

  1. SELECT * FROM stu1 ORDER BY math DESC;
复制代码
3.4.3 查询学生信息,按照英语结果降序排列,假如英语结果一样,再按照数学结果升序排列

  1. SELECT * FROM stu1 ORDER BY english DESC , math ASC;
复制代码
3.5 分组查询

  3.5.1 聚合函数

3.5.1.1 统计班级有多少个学生

  1. SELECT COUNT(id) FROM stu1;
  2. SELECT COUNT(*) FROM stu1;
复制代码
3.5.1.2 查询数学结果最高分

  1. SELECT MAX(math) FROM stu1;
复制代码
3.5.1.3 查询数学结果最低分

  1. SELECT MIN(math) FROM stu1;
复制代码
3.5.1.4 查询数学结果总分

  1. SELECT SUM(math) FROM stu1;
复制代码
3.5.1.5 查询数学结果均匀分

  1. SELECT AVG(math) FROM stu1;
复制代码
3.5.2 分组函数

3.5.2.1 查询男同学和女同学的各自均匀分

  1. SELECT sex,AVG(math) FROM stu1 GROUP BY sex;
复制代码
3.5.2.2 查询男同学和女同学的各自均匀分,以及各自人数

  1. SELECT sex,AVG(math),COUNT(*) FROM stu1 GROUP BY sex;
复制代码
3.5.2.3 查询男同学和女同学的各自均匀分,以及各自人数,要求分数低于80的不参与分组

  1. SELECT sex,AVG(math),COUNT(*) FROM stu1 WHERE math > 80 GROUP BY sex;
复制代码
3.5.2.4 查询男同学和女同学的各自均匀分,以及各自人数,要求分数低于80的不参与分组,分组之后人数大于2

  1. SELECT sex,AVG(math),COUNT(*) FROM stu1 WHERE math > 80 GROUP BY sex HAVING COUNT(*) > 2;
复制代码
3.6 分页查询

  1. SELECT * FROM  stu1;
复制代码
3.6.1 从0开始查询,查询第一页数据

  
  1. SELECT  * FROM stu1 LIMIT 0,3;
复制代码
3.62 每页显示3条数据显示第一页

  1. SELECT  * FROM stu1 LIMIT 0,3;
复制代码
3.6.3 每页显示3条数据显示第二页

  1. SELECT  * FROM stu1 LIMIT 3,3;
复制代码
3.6.4 每页显示3条数据显示第三页

  1. SELECT  * FROM stu1 LIMIT 6,3;
复制代码
3.6.5 每页显示4条数据显示第二页

  1. SELECT  * FROM stu1 LIMIT 4,4;
复制代码
3.7 束缚

3.7.1 字段的束缚

  1. -- 员工表
  2. CREATE TABLE emp(
  3. id INT PRIMARY KEY,/*员工id主键，且自增长*/
  4. ename VARCHAR(32) UNIQUE,/*员工姓名，非空且唯一*/
  5. joindate DATE NOT NULL,/*入职日期非空*/
  6. salary DOUBLE(7,2) NOT NULL,/*薪水，非空*/
  7. bonus DOUBLE(7,2) DEFAULT 0/*奖金，默认为0*/
  8. );
  9. DESC emp;
  10. ALTER TABLE emp CHANGE ename ename VARCHAR(32) CHARACTER set utf8;
  11. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(1,'张三','1999-11-11',8800,5000);
  12. SELECT * FROM emp;
复制代码
3.7.2 演示主键束缚，非空且唯一

  1. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(NULL,'张三','1999-11-11',8800,5000);
  2. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(1,'张三','1999-11-11',8800,5000);
  3. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(2,'李四','1999-11-11',8800,5000);
复制代码
3.7.3 演示非空束缚

  1. DELETE FROM emp WHERE id = 3;
  2. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(3,null,'1999-11-11',8800,5000);
复制代码
3.7.4 演示唯一束缚

  1. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(3,'李四','1999-11-11',8800,5000);
复制代码
3.7.5 对束缚的操作

3.7.5.1 删除束缚

  1. ALTER TABLE emp MODIFY ename VARCHAR(32) CHARACTER set utf8;
复制代码
3.7.5.2 添加束缚

  1. ALTER TABLE emp MODIFY ename VARCHAR(32) NOT NULL ;
  2. DESC emp;
  3. DROP TABLE emp;
复制代码
3.7.5.3 外键束缚（实例演示）

  1. -- 员工表
  2. CREATE TABLE emp(
  3. id INT PRIMARY KEY auto_increment,/*员工id主键，且自增长*/
  4. name VARCHAR(32),/*员工姓名，非空且唯一*/
  5. age INT,
  6. dep_id INT,/*联系到拎一个表*/
  7. -- 添加一个外键约束
  8. CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
  9. );
  10. -- 部门表
  11. CREATE TABLE dept(
  12. id INT PRIMARY KEY auto_increment,
  13. dep_name VARCHAR(32),
  14. address VARCHAR(32)
  15. );
  16. DESC emp;
  17. DESC dept;
  18. DROP TABLE emp;
  19. DROP TABLE dept;
  20. ALTER TABLE emp CHANGE name name VARCHAR(32) CHARACTER set utf8;
  21. ALTER TABLE dept CHANGE dep_name dep_name VARCHAR(32) CHARACTER set utf8;
  22. ALTER TABLE dept CHANGE address address VARCHAR(32) CHARACTER set utf8;
  23. INSERT INTO emp (name,age,dep_id) VALUES
  24. ('张三',20,1),
  25. ('李四',20,1),
  26. ('王五',20,1),
  27. ('赵六',20,2),
  28. ('孙七',22,2),
  29. ('周八',18,2);
  30. INSERT INTO dept (dep_name,address) VALUES
  31. ('研发部','广州'),
  32. ('销售部','深圳');
  33. SELECT * FROM emp;
  34. SELECT * FROM dept;
  35. -- 删除外键
  36. ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;
  37. -- 添加外键
  38. ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id);
复制代码
4 外键

使用数据库，展示所有表
  1. USE db1;
  2. SHOW TABLES;
复制代码
4.1 外键的创建

4.1.1 多对多关系

  1. -- 订单表
  2. CREATE TABLE tb_order(
  3. id INT PRIMARY KEY auto_increment,
  4. payment double(10,2),
  5. payment_type TINYINT,
  6. status TINYINT
  7. );
  8. -- 商品表
  9. CREATE TABLE tb_goods(
  10. id INT PRIMARY KEY auto_increment,
  11. title VARCHAR(100),
  12. price DOUBLE(10,2)
  13. );
  14. -- 中间表
  15. CREATE TABLE tb_order_goods(
  16. id INT PRIMARY KEY auto_increment,
  17. order_id INT,
  18. goods_id INT
  19. );
  20. -- 添加外键
  21. ALTER TABLE tb_order_goods ADD CONSTRAINT fk_order_id FOREIGN KEY(order_id) REFERENCES tb_order(id);
  22. ALTER TABLE tb_order_goods ADD CONSTRAINT fk_goods_id FOREIGN KEY(goods_id) REFERENCES tb_goods(id);
  23. SHOW TABLES;
复制代码
4.1.2 一对一关系

  1. -- 用户表
  2. CREATE TABLE tb_user(
  3. id INT PRIMARY KEY auto_increment,
  4. photo VARCHAR(100),
  5. name VARCHAR(32),
  6. age INT,
  7. sex VARCHAR(4),
  8. desc_id INT UNIQUE,
  9. CONSTRAINT tb_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
  10. );
  11. -- 用户详情表
  12. CREATE TABLE tb_user_desc(
  13. id INT PRIMARY KEY auto_increment,
  14. city VARCHAR(32),
  15. edu VARCHAR(32),
  16. income DOUBLE(7,2),
  17. status TINYINT
  18. );
  19. ALTER TABLE tb_user_desc CHANGE status status VARCHAR(16) CHARACTER set utf8;
  20. INSERT into tb_user_desc(city,edu,income,status) VALUES
  21. ('广州','本科',3000,'单身'),
  22. ('广州','硕士',12000,'单身');
  23. INSERT into tb_user(photo,`name`,age,sex,desc_id) VALUES
  24. ('c盘','林青霞',22,'女',1),
  25. ('d盘','风清扬',24,'男',2);
  26. ALTER TABLE tb_user auto_increment = 1;
  27. SELECT * FROM tb_user;
  28. SELECT * FROM tb_user_desc;
  29. DESC tb_user;
  30. DESC tb_user_desc;
  31. DROP TABLE tb_user;
复制代码
4.1.3 检察所有外键
  1. SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
复制代码
4.2 实例演示训练

  1. -- 音乐专辑表名
  2. CREATE TABLE music(
  3. title VARCHAR(32),/*专辑名*/
  4. alias VARCHAR(32),/*专辑别名*/
  5. image VARCHAR(64),/*封面图片*/
  6. style VARCHAR(16),/*流派（经典、流行、民谣）*/
  7. type VARCHAR(8),/*类型（专辑，单曲）*/
  8. medium VARCHAR(8),/*介质（胶片，cd）*/
  9. publish_time DATE,/*发行时间*/
  10. publisher VARCHAR(8),/*出版者*/
  11. number TINYINT,/*唱片数量*/
  12. barcode BIGINT,/*条形码*/
  13. summary VARCHAR(1024),/*简介*/
  14. artist VARCHAR(32),/*艺术家*/
  15. id INT UNIQUE/*编号，唯一*/
  16. );
  17. -- 曲目表名
  18. CREATE TABLE song(
  19. name VARCHAR(32),/*歌曲名*/
  20. serial_number TINYINT,/*歌曲序号*/
  21. id INT UNIQUE/*编号，唯一*/
  22. );
  23. -- 评论表名
  24. CREATE TABLE review(
  25. content VARCHAR(1024),/*评论内容*/
  26. rating TINYINT,/*评分1-5*/
  27. review datetime,/*评论时间*/
  28. content_user_id INT,
  29. content_music_id INT
  30. );
  31. -- 用户表名
  32. CREATE TABLE user(
  33. username VARCHAR(32),
  34. image VARCHAR(64),
  35. signture VARCHAR(64),
  36. name VARCHAR(32),
  37. id INT PRIMARY KEY
  38. );
  39. -- 展示
  40. DESC music;
  41. DESC song;
  42. DESC review;
  43. DESC user;
  44. -- 删除
  45. DROP TABLE music;
  46. DROP TABLE song;
  47. DROP TABLE review;
  48. DROP TABLE user;
  49. -- 专辑和用户的中间表
  50. CREATE TABLE music_user(
  51. id INT PRIMARY KEY auto_increment,
  52. music_id INT,
  53. user_id INT
  54. );
  55. -- 添加专辑和用户外键
  56. ALTER TABLE music_user ADD CONSTRAINT fk_music_id FOREIGN KEY(music_id) REFERENCES music(id);
  57. ALTER TABLE music_user ADD CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES user(id);
  58. -- 添加短评和用户外键
  59. ALTER TABLE review ADD CONSTRAINT fk_review_user FOREIGN KEY(content_user_id) REFERENCES user(id);
  60. -- 添加短评专辑外键
  61. ALTER TABLE review ADD CONSTRAINT fk_review_music FOREIGN KEY(content_music_id) REFERENCES music(id);
  62. -- 添加曲目和专辑外键
  63. ALTER TABLE song ADD CONSTRAINT fk_song_music FOREIGN KEY(id) REFERENCES music(id);
  64. ALTER TABLE song DROP FOREIGN KEY fk_song_user;
复制代码
逆向化模型

– 多表查询
  1. SELECT * FROM emp;
  2. SELECT * FROM dept;
  3. SELECT * FROM emp,dept;
复制代码
– 产生笛卡尔积（有ab两个集合，去a和b所有的组合情况）
– 消除无效数据
– 查询emp和dept的数据，emp.dep_id = dept.id
– 隐式内连接
  1. SELECT * FROM emp,dept WHERE emp.dep_id = dept.id;
复制代码
– 查询emp的name,age，dept表的dep_name
  1. SELECT emp.`name`,emp.age,dept.dep_name FROM emp,dept WHERE emp.dep_id = dept.id;
复制代码
– 给表起别名
  1. SELECT t1.`name`,t1.age,t2.dep_name FROM emp AS t1,dept AS t2 WHERE t1.dep_id = t2.id;
复制代码
– 显式内连接
  1. SELECT * FROM emp LEFT OUTER JOIN dept ON emp.dep_id = dept.id;
  2. SELECT * FROM emp LEFT /*OUTER*/ JOIN dept ON emp.dep_id = dept.id;
复制代码
– 右外连接
  1. SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.dep_id = dept.id;
  2. SELECT * FROM emp RIGHT /*OUTER*/ JOIN dept ON emp.dep_id = dept.id;
  3. ```# 1 DDL
  5. ## 1.1  显示所包含的数据库
  7. ```mysql
  8. SHOW DATABASES;
复制代码
1.2 创建数据库

  1. CREATE DATABASE db2;
  2. CREATE DATABASE IF NOT EXISTS db2;
复制代码
1.3 删除数据库

  1. DROP DATABASE db2;
  2. drop DATABASE IF EXISTS db2;
复制代码
– 检察当前所使用的数据库
  1. SELECT DATABASE();
复制代码
1.4 使用数据库

  1. use db1;
复制代码
1.4.1 创建表

  1. CREATE TABLE jd_user(
  2. id int,
  3. username VARCHAR(32),
  4. password VARCHAR(32)
  5. );
复制代码
1.4.2 检察表的结构

  1. DESC stu;
复制代码
1.4.3 检察当前数据库下的所有表

  1. USE DATABASE db1;
  2. SHOW TABLES;
复制代码
1.4.4 根本的增删改查

1.4.4.1 删除表

  1. DROP TABLE tb_user;
  2. DROP TABLE IF EXISTS tb_user;
复制代码
1.4.4.2 添加列

  1. ALTER TABLE jd_user ADD address VARCHAR(32);
复制代码
1.4.4.3 修改表名

  1. ALTER TABLE jd_user RENAME TO jd_user;
复制代码
1.4.4.4 修改数据类型

  1. ALTER TABLE jd_user MODIFY address CHAR(32);
  2. DESC jd_user;
复制代码
1.4.4.5 修改列名和数据类型

  1. ALTER TABLE jd_user CHANGE address location VARCHAR(64);
复制代码
1.4.5 查询所有数据
  1. SELECT * FROM jd_user;
  2. SELECT * FROM stu;
复制代码
2 DML

2.1 给指定列添加数据

2.1.1 修改中文列的编码格式(修改列名和数据类型)：

  1. alter table stu change name name varchar(255) character set utf8;
  2. INSERT INTO stu(id,name) VALUES(1,'张三');
复制代码
2.1.2 给所有列添加数据

  1. alter table stu change sex sex varchar(255) character set utf8;
  2. INSERT INTO stu(id,name,sex,birthday,score,email,tel,status) VALUES
  3. (2,'lisa','女','1999-11-11',98.00,'1@qq.com',1123,1);
复制代码
2.1.3 给所有列添加数据，列名的列表可以省略

  1. INSERT INTO stu VALUES(3,'小米','男','1998-10-17',93.00,'2@qq.com',1433,1);
复制代码
2.1.4 批量添加

  1. INSERT INTO stu VALUES
  2. (4,'huawei','男','1998-10-17',93.00,'2@qq.com',1433,1),
  3. (5,'荣耀','男','1998-10-17',93.00,'2@qq.com',1433,1),
  4. (6,'苹果','男','1998-10-17',93.00,'2@qq.com',1433,1);
复制代码
2.2 修改数据

2.2.1 将张三的性别改为男

  1. UPDATE stu SET sex = '男' WHERE name = '张三';
复制代码
2.2.2 将张三的生日改成2000-02-28，结果改成99.00

  1. UPDATE stu SET birthday = '2000-02-28',score = '99.00' WHERE name = '张三';
复制代码
2.2.3 假如update语句没有where条件，则表中的数据全部都被修改

2.3 删除数据

2.3.1 删除小米纪录

  1. DELETE FROM stu WHERE name = '小米';
复制代码
2.4 简朴的创建表，添加数据，查询数据

– 使用数据库
USE db1;
– 创建表
  1. CREATE TABLE stu1 (
  2. id int,
  3. name VARCHAR(32),
  4. age int,
  5. sex VARCHAR(4),
  6. address VARCHAR(64),
  7. math DOUBLE(5,2),
  8. english DOUBLE(5,2),
  9. hire_date DATE
  10. );
复制代码
– 插入数据
  1. ALTER TABLE stu1 CHANGE name name VARCHAR(32) character set utf8;
  2. ALTER TABLE stu1 CHANGE sex sex VARCHAR(4) character set utf8;
  3. ALTER TABLE stu1 CHANGE address address VARCHAR(64) character set utf8;
  4. INSERT INTO stu1(id,name,age,sex,address,math,english,hire_date)
  5. VALUES
  6. (1,'张一',25,'男','杭州',66.00,78.00,'1998-09-09'),
  7. (2,'张二',24,'女','北京',87.00,76.00,'1996-09-09'),
  8. (3,'张三',22,'男','郑州',94.00,65.00,'1997-09-09'),
  9. (4,'张四',23,'男','合肥',69.00,75.00,'1998-09-09'),
  10. (5,'张五',23,'女','无锡',76.00,79.00,'1999-09-09'),
  11. (6,'张六',24,'女','苏州',88.00,94.00,'1998-03-09'),
  12. (7,'张七',21,'男','南通',89.00,90.00,'1998-05-09'),
  13. (8,'张八',22,'男','南充',98.00,90.00,'1998-07-09');
复制代码
– 查询所有数据
  1. SELECT * FROM stu1;
  2. USE db1;
复制代码
3 DQL

3.1 根本查询

3.1.1 查询所有列的数据，列名的列表可以使用*代替

  1. SELECT *FROM stu1;
  2. SELECT `name`,age,sex,address,math,english,hire_date FROM stu1;
复制代码
3.1.2 查询name age 两列

  1. SELECT `name`,age FROM stu1;
复制代码
3.1.3 查询英语分数

  1. SELECT english FROM stu1;
复制代码
3.1.4 去除重复纪录

  1. SELECT DISTINCT english FROM stu1;
复制代码
3.1.5 查询时起别名 as

  1. SELECT name AS 姓名,math AS 数学,english AS 英语 FROM stu1;
复制代码
3.2 条件查询

3.2.1 查询年事大于23岁的学员信息

  1. SELECT * FROM stu1        WHERE age > 23;
复制代码
3.2.2 查询年事>=24岁的学员信息

  1. SELECT * FROM stu1 WHERE age >= 24;
复制代码
3.2.3 查询21<=年事<=23岁的学员信息

  1. SELECT * FROM stu1 WHERE age>=21 AND age <= 23;
  2. SELECT * FROM stu1 WHERE age BETWEEN 21 AND 23;
复制代码
3.2.4 查询入学时间在1997-05-09–1998-07-09之间的学员信息

  1. SELECT * FROM stu1 WHERE hire_date BETWEEN '1997-05-09' AND '1998-07-09';
复制代码
3.2.5 查询年事等于21岁的学员信息

  1. SELECT * FROM stu1 WHERE age = 21;
复制代码
3.2.6 查询年事等于21岁或者年事等于24岁或者年事等于25岁的学员信息

  1. SELECT * FROM stu1 WHERE age = 21 OR age = 24 OR age = 25;
  2. SELECT * FROM stu1 WHERE age IN(21,24,25);
复制代码
3.2.7 查询英语结果为空null的学员信息

  1. SELECT * FROM stu1 WHERE english IS NOT null;
复制代码
3.3 模糊查询

  3.3.1 查询姓’张’的学员信息

  1. SELECT * FROM stu1 WHERE `name` LIKE '张%';
  2. -- 查询第二个字是'三'的学员信息
  3. SELECT * FROM stu1 WHERE `name` LIKE '_三%';
  4. -- 查询名字中含有'四'的学员信息
  5. SELECT * FROM stu1 WHERE `name` LIKE '%四%';
复制代码
3.4 排序查询

  3.4.1 查询学生信息,按照年事升序排列

  1. SELECT * FROM stu1 ORDER BY age ASC;
复制代码
3.4.2 查询学生信息,按照数学结果降序排列

  1. SELECT * FROM stu1 ORDER BY math DESC;
复制代码
3.4.3 查询学生信息,按照英语结果降序排列,假如英语结果一样,再按照数学结果升序排列

  1. SELECT * FROM stu1 ORDER BY english DESC , math ASC;
复制代码
3.5 分组查询

  3.5.1 聚合函数

3.5.1.1 统计班级有多少个学生

  1. SELECT COUNT(id) FROM stu1;
  2. SELECT COUNT(*) FROM stu1;
复制代码
3.5.1.2 查询数学结果最高分

  1. SELECT MAX(math) FROM stu1;
复制代码
3.5.1.3 查询数学结果最低分

  1. SELECT MIN(math) FROM stu1;
复制代码
3.5.1.4 查询数学结果总分

  1. SELECT SUM(math) FROM stu1;
复制代码
3.5.1.5 查询数学结果均匀分

  1. SELECT AVG(math) FROM stu1;
复制代码
3.5.2 分组函数

3.5.2.1 查询男同学和女同学的各自均匀分

  1. SELECT sex,AVG(math) FROM stu1 GROUP BY sex;
复制代码
3.5.2.2 查询男同学和女同学的各自均匀分,以及各自人数

  1. SELECT sex,AVG(math),COUNT(*) FROM stu1 GROUP BY sex;
复制代码
3.5.2.3 查询男同学和女同学的各自均匀分,以及各自人数,要求分数低于80的不参与分组

  1. SELECT sex,AVG(math),COUNT(*) FROM stu1 WHERE math > 80 GROUP BY sex;
复制代码
3.5.2.4 查询男同学和女同学的各自均匀分,以及各自人数,要求分数低于80的不参与分组,分组之后人数大于2

  1. SELECT sex,AVG(math),COUNT(*) FROM stu1 WHERE math > 80 GROUP BY sex HAVING COUNT(*) > 2;
复制代码
3.6 分页查询

  1. SELECT * FROM  stu1;
复制代码
3.6.1 从0开始查询,查询第一页数据

  
  1. SELECT  * FROM stu1 LIMIT 0,3;
复制代码
3.62 每页显示3条数据显示第一页

  1. SELECT  * FROM stu1 LIMIT 0,3;
复制代码
3.6.3 每页显示3条数据显示第二页

  1. SELECT  * FROM stu1 LIMIT 3,3;
复制代码
3.6.4 每页显示3条数据显示第三页

  1. SELECT  * FROM stu1 LIMIT 6,3;
复制代码
3.6.5 每页显示4条数据显示第二页

  1. SELECT  * FROM stu1 LIMIT 4,4;
复制代码
3.7 束缚

3.7.1 字段的束缚

  1. -- 员工表
  2. CREATE TABLE emp(
  3. id INT PRIMARY KEY,/*员工id主键，且自增长*/
  4. ename VARCHAR(32) UNIQUE,/*员工姓名，非空且唯一*/
  5. joindate DATE NOT NULL,/*入职日期非空*/
  6. salary DOUBLE(7,2) NOT NULL,/*薪水，非空*/
  7. bonus DOUBLE(7,2) DEFAULT 0/*奖金，默认为0*/
  8. );
  9. DESC emp;
  10. ALTER TABLE emp CHANGE ename ename VARCHAR(32) CHARACTER set utf8;
  11. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(1,'张三','1999-11-11',8800,5000);
  12. SELECT * FROM emp;
复制代码
3.7.2 演示主键束缚，非空且唯一

  1. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(NULL,'张三','1999-11-11',8800,5000);
  2. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(1,'张三','1999-11-11',8800,5000);
  3. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(2,'李四','1999-11-11',8800,5000);
复制代码
3.7.3 演示非空束缚

  1. DELETE FROM emp WHERE id = 3;
  2. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(3,null,'1999-11-11',8800,5000);
复制代码
3.7.4 演示唯一束缚

  1. INSERT INTO emp(id,ename,joindate,salary,bonus) VALUES(3,'李四','1999-11-11',8800,5000);
复制代码
3.7.5 对束缚的操作

3.7.5.1 删除束缚

  1. ALTER TABLE emp MODIFY ename VARCHAR(32) CHARACTER set utf8;
复制代码
3.7.5.2 添加束缚

  1. ALTER TABLE emp MODIFY ename VARCHAR(32) NOT NULL ;
  2. DESC emp;
  3. DROP TABLE emp;
复制代码
3.7.5.3 外键束缚（实例演示）

  1. -- 员工表
  2. CREATE TABLE emp(
  3. id INT PRIMARY KEY auto_increment,/*员工id主键，且自增长*/
  4. name VARCHAR(32),/*员工姓名，非空且唯一*/
  5. age INT,
  6. dep_id INT,/*联系到拎一个表*/
  7. -- 添加一个外键约束
  8. CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
  9. );
  10. -- 部门表
  11. CREATE TABLE dept(
  12. id INT PRIMARY KEY auto_increment,
  13. dep_name VARCHAR(32),
  14. address VARCHAR(32)
  15. );
  16. DESC emp;
  17. DESC dept;
  18. DROP TABLE emp;
  19. DROP TABLE dept;
  20. ALTER TABLE emp CHANGE name name VARCHAR(32) CHARACTER set utf8;
  21. ALTER TABLE dept CHANGE dep_name dep_name VARCHAR(32) CHARACTER set utf8;
  22. ALTER TABLE dept CHANGE address address VARCHAR(32) CHARACTER set utf8;
  23. INSERT INTO emp (name,age,dep_id) VALUES
  24. ('张三',20,1),
  25. ('李四',20,1),
  26. ('王五',20,1),
  27. ('赵六',20,2),
  28. ('孙七',22,2),
  29. ('周八',18,2);
  30. INSERT INTO dept (dep_name,address) VALUES
  31. ('研发部','广州'),
  32. ('销售部','深圳');
  33. SELECT * FROM emp;
  34. SELECT * FROM dept;
  35. -- 删除外键
  36. ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;
  37. -- 添加外键
  38. ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id);
复制代码
4 外键

使用数据库，展示所有表
  1. USE db1;
  2. SHOW TABLES;
复制代码
4.1 外键的创建

4.1.1 多对多关系

  1. -- 订单表
  2. CREATE TABLE tb_order(
  3. id INT PRIMARY KEY auto_increment,
  4. payment double(10,2),
  5. payment_type TINYINT,
  6. status TINYINT
  7. );
  8. -- 商品表
  9. CREATE TABLE tb_goods(
  10. id INT PRIMARY KEY auto_increment,
  11. title VARCHAR(100),
  12. price DOUBLE(10,2)
  13. );
  14. -- 中间表
  15. CREATE TABLE tb_order_goods(
  16. id INT PRIMARY KEY auto_increment,
  17. order_id INT,
  18. goods_id INT
  19. );
  20. -- 添加外键
  21. ALTER TABLE tb_order_goods ADD CONSTRAINT fk_order_id FOREIGN KEY(order_id) REFERENCES tb_order(id);
  22. ALTER TABLE tb_order_goods ADD CONSTRAINT fk_goods_id FOREIGN KEY(goods_id) REFERENCES tb_goods(id);
  23. SHOW TABLES;
复制代码
4.1.2 一对一关系

  1. -- 用户表
  2. CREATE TABLE tb_user(
  3. id INT PRIMARY KEY auto_increment,
  4. photo VARCHAR(100),
  5. name VARCHAR(32),
  6. age INT,
  7. sex VARCHAR(4),
  8. desc_id INT UNIQUE,
  9. CONSTRAINT tb_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
  10. );
  11. -- 用户详情表
  12. CREATE TABLE tb_user_desc(
  13. id INT PRIMARY KEY auto_increment,
  14. city VARCHAR(32),
  15. edu VARCHAR(32),
  16. income DOUBLE(7,2),
  17. status TINYINT
  18. );
  19. ALTER TABLE tb_user_desc CHANGE status status VARCHAR(16) CHARACTER set utf8;
  20. INSERT into tb_user_desc(city,edu,income,status) VALUES
  21. ('广州','本科',3000,'单身'),
  22. ('广州','硕士',12000,'单身');
  23. INSERT into tb_user(photo,`name`,age,sex,desc_id) VALUES
  24. ('c盘','林青霞',22,'女',1),
  25. ('d盘','风清扬',24,'男',2);
  26. ALTER TABLE tb_user auto_increment = 1;
  27. SELECT * FROM tb_user;
  28. SELECT * FROM tb_user_desc;
  29. DESC tb_user;
  30. DESC tb_user_desc;
  31. DROP TABLE tb_user;
复制代码
4.1.3 检察所有外键
  1. SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
复制代码
4.2 实例演示训练

  1. -- 音乐专辑表名
  2. CREATE TABLE music(
  3. title VARCHAR(32),/*专辑名*/
  4. alias VARCHAR(32),/*专辑别名*/
  5. image VARCHAR(64),/*封面图片*/
  6. style VARCHAR(16),/*流派（经典、流行、民谣）*/
  7. type VARCHAR(8),/*类型（专辑，单曲）*/
  8. medium VARCHAR(8),/*介质（胶片，cd）*/
  9. publish_time DATE,/*发行时间*/
  10. publisher VARCHAR(8),/*出版者*/
  11. number TINYINT,/*唱片数量*/
  12. barcode BIGINT,/*条形码*/
  13. summary VARCHAR(1024),/*简介*/
  14. artist VARCHAR(32),/*艺术家*/
  15. id INT UNIQUE/*编号，唯一*/
  16. );
  17. -- 曲目表名
  18. CREATE TABLE song(
  19. name VARCHAR(32),/*歌曲名*/
  20. serial_number TINYINT,/*歌曲序号*/
  21. id INT UNIQUE/*编号，唯一*/
  22. );
  23. -- 评论表名
  24. CREATE TABLE review(
  25. content VARCHAR(1024),/*评论内容*/
  26. rating TINYINT,/*评分1-5*/
  27. review datetime,/*评论时间*/
  28. content_user_id INT,
  29. content_music_id INT
  30. );
  31. -- 用户表名
  32. CREATE TABLE user(
  33. username VARCHAR(32),
  34. image VARCHAR(64),
  35. signture VARCHAR(64),
  36. name VARCHAR(32),
  37. id INT PRIMARY KEY
  38. );
  39. -- 展示
  40. DESC music;
  41. DESC song;
  42. DESC review;
  43. DESC user;
  44. -- 删除
  45. DROP TABLE music;
  46. DROP TABLE song;
  47. DROP TABLE review;
  48. DROP TABLE user;
  49. -- 专辑和用户的中间表
  50. CREATE TABLE music_user(
  51. id INT PRIMARY KEY auto_increment,
  52. music_id INT,
  53. user_id INT
  54. );
  55. -- 添加专辑和用户外键
  56. ALTER TABLE music_user ADD CONSTRAINT fk_music_id FOREIGN KEY(music_id) REFERENCES music(id);
  57. ALTER TABLE music_user ADD CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES user(id);
  58. -- 添加短评和用户外键
  59. ALTER TABLE review ADD CONSTRAINT fk_review_user FOREIGN KEY(content_user_id) REFERENCES user(id);
  60. -- 添加短评专辑外键
  61. ALTER TABLE review ADD CONSTRAINT fk_review_music FOREIGN KEY(content_music_id) REFERENCES music(id);
  62. -- 添加曲目和专辑外键
  63. ALTER TABLE song ADD CONSTRAINT fk_song_music FOREIGN KEY(id) REFERENCES music(id);
  64. ALTER TABLE song DROP FOREIGN KEY fk_song_user;
复制代码
逆向化模型

– 多表查询
  1. SELECT * FROM emp;
  2. SELECT * FROM dept;
  3. SELECT * FROM emp,dept;
复制代码
– 产生笛卡尔积（有ab两个集合，去a和b所有的组合情况）
– 消除无效数据
– 查询emp和dept的数据，emp.dep_id = dept.id
– 隐式内连接
  1. SELECT * FROM emp,dept WHERE emp.dep_id = dept.id;
复制代码
– 查询emp的name,age，dept表的dep_name
  1. SELECT emp.`name`,emp.age,dept.dep_name FROM emp,dept WHERE emp.dep_id = dept.id;
复制代码
– 给表起别名
  1. SELECT t1.`name`,t1.age,t2.dep_name FROM emp AS t1,dept AS t2 WHERE t1.dep_id = t2.id;
复制代码
– 显式内连接
  1. SELECT * FROM emp LEFT OUTER JOIN dept ON emp.dep_id = dept.id;
  2. SELECT * FROM emp LEFT /*OUTER*/ JOIN dept ON emp.dep_id = dept.id;
复制代码
– 右外连接
  1. SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.dep_id = dept.id;
  2. SELECT * FROM emp RIGHT /*OUTER*/ JOIN dept ON emp.dep_id = dept.id;
复制代码
总结

  以上就是SQL语句大全的全部内容，希望对你有所资助。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

惊雷无声

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表