首页 > 代码库 > 复习下SQL

复习下SQL

2024-09-14 17:51:19   209人阅读

最近复习SQL,发现好多东西已经忘了，好记性不如烂笔头真乃至理名言，
sql:结构化查询语言(structed query language)
1.创建一个数据库

CREATE DATABASE my_data_base;

2.使用刚刚创建的库

USE my_data_base;

3.创建一个名为teacher的表格

CREATE TABLE teacher(    name VARCHAR(10),    age  INT,    birthday DATE);

3.1.可以检查下创建的表

DESC teacher;

3.2.如果想删除创建的表

DROP TABLE teacher;

4.加入数据

INSERT INTO teacher(    name, age, birthday)VALUES(    ‘Tom‘, 29, ‘1988-09-25‘ );

5.看看加入数据后，表格的样子

SELECT * FROM teacher;

6.给数据库搜索提供条件

SELECT * FROM teacher WHERE name = ‘Tom‘;

7.使用AND和OR结合查询

SELECT * FROM teacher WHERE name = ‘Tom‘ANDage = 29;

8.寻找某个值为NULL的数据

SELECT * FROM teacher WHERE name IS NULL;

9.使用通配符来查找,%是任意数量的未知字符的替身，_是一个未知字符的替身

SELECT * FROM teacherWHERE name LIKE ‘%o_‘;

10.使用范围起止值来查询

SELECT * FROM teacherWHERE age BETWEEN 20 AND 30;

11.使用列值匹配集合的方式来查询

SELECT * FROM teacherWHERE name IN (‘Tom‘, ‘Tony‘, ‘Jone‘);

12.反转查询,NOT一定要紧跟在WHERE的后面

SELECT * FROM teacherWHERE NOTname IN (‘Tom‘, ‘Tony‘, ‘Jone‘);

13.删除数据

DELETE FROM teacherWHEREname = ‘Tom‘;

14.更新数据

UPDATE teacherSET name = ‘Mike‘WHERE name = ‘Tony‘;

15.添加主键

ALTER TABLE teacherADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST,ADD PRIMARY KEY (id);

15.1.创建主键，主键不可以为NULL，不可以被修改

CREATE TABLE teacher(    id   INT NOT NULL AUTO_INCREMENT,    name VARCHAR(10),    age  INT,    birthday DATE,    PRIMARY KEY (id));

16.改变表的名字

ALTER TABLE teacher RENAME TO student;

17.改变表的列名称类型

ALTER TABLE studentCHANGE COLUMN name school VARCHAR(20) NOT NULL;

18.只改变列的类型不会干涉它的名称

ALTER TABLE studentMODIFY COLUMN school VARCHAR(120);

19.卸除某列

ALTER TABLE studentDROP COLUMN school;

20.排序

SELECT * FROM studentORDER BY age DESC;

21.相加函数

SELECT SUM(age)FROM student;

22.分组相加

SELECT id, SUM(age) FROM studentGROUP BY id;

23.平均函数

SELECT AVG(age)FROM student;

24.最大最小值

SELECT MAX(age) FROM student;SELECT MIN(age) FROM student;

25.指定列中的行数

SELECT COUNT(age) FROM student;

26.选出与众不同的值

SELECT DISTINCT age FROM student;

27.限制查询结果的数量 第一个数为查询起始处，第二个数为返回查询结果的数量

SELECT * FROM student LIMIT 0, 1;

 

复习下SQL


联系
我们