SQL语句总结

最新推荐文章于 2023-12-02 10:14:02 发布

置顶只爱007

最新推荐文章于 2023-12-02 10:14:02 发布

阅读量1.1k

点赞数 2

文章标签： sql 面试数据库

本文链接：https://blog.csdn.net/CZY2106/article/details/121258082

版权

首先用DOS窗口对数据库进行增删改查

1.登入mysql

mysql -uroot -proot

2.查询你的所有数据库（注意命令输完 ; 结束）

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| czy                |
| information_schema |
| xka                |
| mysql              |
| performance_schema |
| test               |
+--------------------+

3.创建数据库（不能同名）

MariaDB [(none)]> create database dede;
Query OK, 1 row affected (1.743 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| czy                |
| dede               |
| information_schema |
| xka                |
| mysql              |
| performance_schema |
| test               |
+--------------------+

4.删除数据库（谨慎操作）

MariaDB [(none)]> drop database dede;
Query OK, 0 rows affected (0.390 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| czy                |
| information_schema |
| xka                |
| mysql              |
| performance_schema |
| test               |
+--------------------+

5.使用指定数据库并查询数据库中所有表

MariaDB [(none)]> use dede;
Database changed
MariaDB [dede]> show tables;
Empty set (0.001 sec)  #空的数据库

6.创建学生表并查看

MariaDB [dede]> create table student(
    -> id int(20),
    -> name varchar(30),
    -> address varchar(100),
    -> age int(5)
    -> );
Query OK, 0 rows affected (0.920 sec)

MariaDB [dede]> desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(20)      | YES  |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| age     | int(5)       | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.013 sec)

7.修改表添加新字段

MariaDB [dede]> alter table student add column sex varchar(5);
Query OK, 0 rows affected (1.884 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dede]> desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(20)      | YES  |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| age     | int(5)       | YES  |     | NULL    |       |
| sex     | varchar(5)   | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.007 sec)

8.在指定位置添加新字段

MariaDB [dede]> alter table student add column tel int(30) after age;
Query OK, 0 rows affected (1.143 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dede]> desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(20)      | YES  |     | NULL    |       |
| name    | varchar(30)  | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| age     | int(5)       | YES  |     | NULL    |       |
| tel     | int(30)      | YES  |     | NULL    |       |
| sex     | varchar(5)   | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.008 sec)

9.删除表里的字段

MariaDB [dede]> alter table student drop column address,drop column sex;
Query OK, 0 rows affected (1.037 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dede]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(5)      | YES  |     | NULL    |       |
| tel   | int(30)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.011 sec)

10.删除表

MariaDB [dede]> drop table student; #执行不可能执行的

11.查看表里的所有记录

MariaDB [dede]> select * from student;
Empty set (0.001 sec) #急啥！

12.招骗几个学生（嘘~）

MariaDB [dede]> insert into student values(18,'嘚嘚',18657984);
MariaDB [dede]> insert into student values(12,'luoli',18498974);
MariaDB [dede]> insert into student values(22,'虞姬',1848946574);
Query OK, 1 row affected (0.203 sec)

MariaDB [dede]> select * from student;
+------+-------+------------+
| age  | name  | tel        |
+------+-------+------------+
|   18 | 嘚嘚  |   18657984 |
|   12 | luoli |   18498974 |
|   22 | 虞姬  | 1848946574 |
+------+-------+------------+
3 rows in set (0.000 sec)

注意：如果中文输入报错，可进行如下操作

12.1 首先查看数据库的字符编码

MariaDB [dede]> show variables like 'char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | gbk                            |
| character_set_connection | gbk                            |
| character_set_database   | latin1                         |
| character_set_filesystem | binary                         |
| character_set_results    | gbk                            |
| character_set_server     | latin1                         |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | D:\mariadb10.6\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.028 sec)

12.2 统一修改为gbk格式

MariaDB [dede]> set character_set_client = 'gbk' ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [dede]> set character_set_connection = 'gbk' ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [dede]> set character_set_results= 'gbk' ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [dede]> set character_set_server= 'gbk' ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [dede]> set character_set_database= 'gbk' ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [dede]> show variables like 'char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | gbk                            |
| character_set_connection | gbk                            |
| character_set_database   | gbk                            |
| character_set_filesystem | binary                         |
| character_set_results    | gbk                            |
| character_set_server     | gbk                            |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | D:\mariadb10.6\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.001 sec)

12.3 修改对应的数据表的字符编码

                             表名              字段
MariaDB [dede]> alter table student modify name varchar(50) character set gbk;
Query OK, 1 row affected (0.810 sec)

13.修改学生信息

MariaDB [dede]> update student set age=18;
Query OK, 2 rows affected (0.029 sec)
Rows matched: 3  Changed: 2  Warnings: 0

MariaDB [dede]> select * from student;
+------+-------+------------+
| age  | name  | tel        |
+------+-------+------------+
|   18 | 嘚嘚  |   18657984 |
|   18 | luoli |   18498974 |
|   18 | 虞姬  | 1848946574 |
+------+-------+------------+
3 rows in set (0.000 sec)

14.修改指定学生信息

MariaDB [dede]> update student set age=81 where name='嘚嘚';
Query OK, 1 row affected (0.077 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [dede]> select * from student;
+------+-------+------------+
| age  | name  | tel        |
+------+-------+------------+
|   81 | 嘚嘚  |   18657984 |
|   18 | luoli |   18498974 |
|   18 | 虞姬  | 1848946574 |
+------+-------+------------+
3 rows in set (0.000 sec)

15.手抖删除了所有学生信息

MariaDB [dede]> delete from student;

16.开除某个学生

MariaDB [dede]> delete from student where name='deed';

17.学校倒闭了

换个城市用sqlyog建个小海腾学校

1.添加主键约束（字段值必须唯一，值不能为null）

CREATE TABLE student1(id INT PRIMARY KEY)

2.交给数据库自增（不需要手动输入id值）

CREATE TABLE student2(id INT PRIMARY KEY AUTO_INCREMENT)

3.添加非空约束（该字段不能为null）

CREATE TABLE student3(id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL)

4.唯一约束（值必须唯一）

CREATE TABLE student4(id INT PRIMARY KEY AUTO_INCREMENT,
tel INT(100) UNIQUE)

5.创个XHT班

CREATE TABLE XHT(id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),tel INT(100) UNIQUE)

INSERT INTO XHT VALUES(NULL,'大娃',111111)
INSERT INTO XHT VALUES(NULL,'二娃',222222)
INSERT INTO XHT VALUES(NULL,'三娃',333333)
INSERT INTO XHT VALUES(NULL,'四娃',444444)
INSERT INTO XHT VALUES(NULL,'五娃',555555)
INSERT INTO XHT VALUES(NULL,'六娃',666666)
INSERT INTO XHT VALUES(NULL,'七娃',777777)
INSERT INTO XHT VALUES(NULL,'aaa',888888)
INSERT INTO XHT VALUES(NULL,'BBB',999999)
INSERT INTO XHT VALUES(NULL,'ccc',123456)
INSERT INTO XHT VALUES(NULL,NULL,159258)
INSERT INTO XHT VALUES(NULL,NULL,789456,99.48)

查询类

6.查询娃娃信息

SELECT * FROM xht           #查询所有娃娃信息
SELECT NAME FROM xht        #查询娃娃的指定信息
SELECT NAME,tel FROM xht    #查询娃娃的多个信息

7.添加字段大小写

SELECT NAME,UPPER(NAME) FROM xht;  #指定字段大写
SELECT NAME,LOWER(NAME) FROM xht;  #指定字段小写

8.查询字段长度

SELECT NAME,LENGTH(NAME),tel,LENGTH(tel) FROM xht

9.截取字段的长度

SELECT tel,SUBSTR(tel,3) FROM xht     #从第三个开始包含第三个
SELECT tel,SUBSTR(tel,3,6) FROM xht   #从第三个开始，第六个结束，含头含尾

10.拼接字段内容

SELECT NAME,CONCAT(NAME,'小宝贝')FROM xht

11.替换字符

SELECT NAME,REPLACE(NAME,'ccc','ddd')FROM xht

12.null值替换

SELECT NAME,IFNULL(NAME,'***')FROM xht

13.对小数的取舍

SELECT comm,ROUND(comm)FROM xht   #四舍五入  99.48 -> 99
SELECT comm,CEIL(comm)FROM xht    #向上取整  99.48 -> 100
SELECT comm,FLOOR(comm)FROM xht   #向下取整  99.48 -> 99

14.对日期的处理

SELECT NOW(),YEAR(NOW()),MONTH(NOW()),DAY(NOW())      #系统时间 年月日
SELECT NOW(),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())  #系统时间 时分秒

15.数据去重

SELECT DISTINCT NAME FROM xht

16.查询id为5的宝儿

SELECT * FROM xht WHERE id=5

17.查询id在5号后的宝儿

SELECT * FROM xht WHERE id>5

18.查询id在5号后名字为aaa的宝儿

SELECT * FROM xht WHERE id>5 AND NAME='aaa'

19.查询指定娃子

SELECT * FROM xht WHERE NAME IN('大娃','五娃')        #找到老大，老五的信息
SELECT * FROM xht WHERE NAME NOT IN('大娃','五娃')    #找到除了老大，老五的信息

20.模糊查询

SELECT * FROM xht WHERE NAME LIKE '三%'     #以三开头
SELECT * FROM xht WHERE NAME LIKE '%a%'     #中间包含a
SELECT * FROM xht WHERE NAME LIKE 'c%'      #以c结尾

21.null数据查询

SELECT * FROM xht WHERE comm IS NULL;         #没有奖学金的仔
SELECT * FROM xht WHERE comm IS NOT NULL;     #有奖学金的仔

22.范围内的数据

SELECT * FROM xht WHERE id>3 AND id<6         #（3,6）
SELECT * FROM xht WHERE id BETWEEN 3 AND 6    #[3,6]

23.查询指定条数

SELECT * FROM xht LIMIT 2      #查询头两条
SELECT * FROM xht LIMIT 2,4    #从第二条开始查后面四条数据，不包括第二条

24.排序（asc升序，desc降序）

SELECT * FROM xht ORDER BY id ASC     #默认升序
SELECT * FROM xht ORDER BY id DESC    #降序
SELECT * FROM xht ORDER BY NAME       #按名字字母排序，汉字排序时会查utf8里对应的数字,按照数字升序排序
SELECT * FROM xht ORDER BY tel        #按数字大小排序

25.聚合函数：count(),max(),min(),avg(),sun()

SELECT MAX(id) FROM xht      #最大值
SELECT MIN(id) FROM xht      #最小值
SELECT COUNT(id) FROM xht    #总数
SELECT SUM(id) FROM xht      #总和
SELECT AVG(id) FROM xht      #平均数

#没错 就是懒

26.聚合+分组

SELECT 班级,AVG(班费) FROM 表名 GROUP BY 班级    #统计每个班级的平均班费
SELECT 班级,COUNT(1) FROM 表名 GROUP BY 班级     #统计每个班的人数
SELECT YEAR(hiredate),COUNT(1) FROM 表名 GROUP BY YEAR(hiredate)   #统计每年入学人数
SELECT 班级,MAX(成绩) FROM 表名 GROUP BY 班级     #统计每个班成绩最高的
SELECT MAX(成绩) a,班级 FROM 表名 GROUP BY 班级 HAVING a>90   #统计每个班成绩最高并大于90分的
SELECT COUNT(1),YEAR(hiredate) a FROM 表名 GROUP BY a HAVING COUNT(1)>1   #统计每年入学人数,起码有一个人

27.字段约束

CREATE TABLE student5(
 id INT PRIMARY KEY AUTO_INCREMENT,
 sex VARCHAR(10) DEFAULT '女' #默认约束,设置默认值
)

CREATE TABLE student6(
 id INT PRIMARY KEY AUTO_INCREMENT,
 age INT,
 CHECK(age>0)#检查约束,age录入不合法数据时无法保存
)

#外键约束:防止了冗余的数据,通过外键来描述两张表的关系
#特点是:当子表中添加数据时,子表的主键值 必须 取自主表!
       #当主表删除数据时,子表没有相关的记录
CREATE TABLE a(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20),
 PASSWORD VARCHAR(20)
)
CREATE TABLE b(
 user_id INT PRIMARY KEY,
 address VARCHAR(100) ,
 #描述和a表的关系,外键
#语法:foreign key(本表的主键名) references 对方表名(对方表的主键)
 FOREIGN KEY(user_id) REFERENCES a(id) #创建外键
)

索引（这一块比较模糊）

提高查询效率，单独生成索引

单值索引：一个索引只包含一个字段

唯一索引：一个索引只包含一个字段，但字段的值不能重复

复合索引：一个索引可以包含多个字段

创建索引(经常按照指定字段查询) + 使用索引

28.查看索引

SHOW INDEX FROM 表名    #主键自带索引

29.创建索引

CREATE INDEX 索引名 ON 表名(字段)                #创建单值索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段)         #创建唯一索引
CREATE INDEX 索引名 ON 表名(字段1,字段2，字段3)   #创建复合索引

30.删除索引

ALTER TABLE 表名 DROP INDEX 索引名

31.复合索引（最左特性）

EXPLAIN SELECT * FROM 表名 WHERE 字段1='**'                 #生效
EXPLAIN SELECT * FROM 表名 WHERE 字段1='**'AND 字段2='**'   #生效
EXPLAIN SELECT * FROM 表名 WHERE 字段2='**'                 #失效	
EXPLAIN SELECT * FROM 表名 WHERE 字段2='**'AND 字段3='**'   #失效 
EXPLAIN SELECT * FROM 表名 WHERE 字段2='**' AND 字段1='**'  #生效

32.查询sql的执行计划（执行语句前加explain关键词即可）

EXPLAIN SELECT * FROM xht WHERE NAME='二娃'

视图

如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询；

视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对基表中的其它列起到安全和保密的作用，可以限制数据访问。

33.创建视图

CREATE VIEW a AS SELECT * FROM xht WHERE NAME LIKE '二%'

34.使用视图

SELECT * FROM a

多表联查

创建多表

CREATE TABLE courses(
cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(3) NOT NULL,
PRIMARY KEY (cno)
)；

CREATE TABLE scores(
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10,1) NOT NULL,
PRIMARY KEY (sno, cno)
);

CREATE TABLE students(
sno VARCHAR(3) NOT NULL, 
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME, 
class VARCHAR(5), 
PRIMARY KEY (sno)
);

CREATE TABLE teachers(
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4),
tsex VARCHAR(2),
tbirthday DATETIME,
prof VARCHAR(6),
depart VARCHAR(10),
PRIMARY KEY (tno)
)；

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陈冰','女','1977-08-14','助教','电子工程系');
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模拟电路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率论' ,831);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

35.笛卡尔积（select * from 表名1,表名2,表名3）

SELECT * FROM 表1，表2 WHERE 表1.字段名=表2.字段名 AND 表1.字段名=xx
SELECT 表1.* FROM 表1，表2 WHERE 表1.字段名=表2.字段名 AND 表1.字段名=xx

36.连接查询（内连接inner join，左连接left join，右连接right join）

SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段名=表2.字段名    #取共同条件
SELECT * FROM 表1 left JOIN 表2 ON 表1.字段名=表2.字段名     #以左边为基准取共同条件，不满足用null填充
SELECT * FROM 表1 right JOIN 表2 ON 表1.字段名=表2.字段名    #以右边为基准取共同条件，不满足用null填充

37.子查询/嵌套查询

#练习1:查询学员李军的总得分(students/scores)
#先查询学生表,根据学员名字查编号
SELECT sno FROM students WHERE sname='李军'#101
#再根据编号查得分表里的分数
SELECT SUM(degree) FROM scores WHERE sno=101
#子查询
SELECT SUM(degree) FROM scores WHERE sno=(
  SELECT sno FROM students WHERE sname='李军'
)

#练习2:查询陈冰老师能讲解的课程的名称(teachers/courses)
#先查老师表,根据名字查编号
SELECT tno FROM teachers WHERE tname='陈冰' #831
#再根据编号查课程名称
SELECT cname FROM courses WHERE tno=831
#子查询
SELECT cname FROM courses WHERE tno<(
  SELECT tno FROM teachers WHERE tname='陈冰'
)

38.更新数据

UPDATE 表名 SET 字段名1=值1
UPDATE 表名 SET 字段名1=值1 WHERE 条件

只爱007

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL语句总结

首先用DOS窗口对数据库进行增删改查1.登入mysqlmysql -uroot -proot2.查询你的所有数据库（注意命令输完 ; 结束）MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| czy || information_schema || xka ||
复制链接

扫一扫