MySQL | SQL语句实例大全

SQL语句实例大全

SQL语句分类

1. DDL(Data Definition Language)数据定义语言
   用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter等
   DDL参考
2. DML(Data Manipulation Language)数据操作语言
   用来对数据库中表的数据进行增删改。关键字：insert, delete, update等
3. DQL(Data Query Language)数据查询语言
   用来查询数据库中表的记录(数据)。关键字：select, where等
4. DCL(Data Control Language)数据控制语言(了解)
   用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE等
   注：重点关注DML、DQL；实际开发中我们可以使用数据库软件来创建数据库、表、列等（DDL了解即可）；DCL为DBA关注的事情。

DML Test

CREATE TABLE student1(
id INT,
NAME VARCHAR(20),
birthday DATE
)

INSERT INTO student1 (id,NAME,birthday) VALUES(1,'zhangsan', '2022-1-20')

INSERT INTO student1 VALUES(2,'lisi','2022-1-2')

INSERT INTO student1 VALUES(3,'wangmazi','2022-1-3'),(4,'zhaoliu','2012-1-2')

INSERT INTO student1 (id,NAME) VALUES(5, 'sunqi')

UPDATE student1 SET NAME='lisi'

UPDATE student1 SET birthday='1998-1-1' WHERE id=5

UPDATE student1 SET id=1,NAME='zhangsan' WHERE id=1

DELETE FROM student1

DELETE FROM student1 WHERE id=5

SELECT * FROM student1

SELECT id,NAME,birthday FROM student1

SELECT NAME FROM student1

DQL Test

CREATE TABLE student3 ( 
id INT, NAME VARCHAR(20),
age INT, sex VARCHAR(5), 
address VARCHAR(100), 
math INT, 
english INT 
)

INSERT INTO student3(id,NAME,age,sex,address,math,english) 
VALUES (1,'马云',55,'男','杭 州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳 岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香 港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65)

SELECT * FROM student3 WHERE math>80

SELECT * FROM student3 WHERE english<=80

SELECT * FROM student3 WHERE age=20

SELECT * FROM student3 WHERE age!=20
SELECT * FROM student3 WHERE age<>20

SELECT * FROM student3 WHERE age>35 AND sex='男'
SELECT * FROM student3 WHERE age>35 && sex='男'

SELECT * FROM student3 WHERE age>35 OR sex='男'
SELECT * FROM student3 WHERE age>35 || sex='男'

SELECT * FROM student3 WHERE id=1 OR id=3 OR id=5

SELECT * FROM student3 WHERE NAME LIKE '马%'

SELECT * FROM student3 WHERE NAME LIKE '%德%'

SELECT * FROM student3 WHERE NAME LIKE '马__'

SELECT COUNT(*) FROM student3

SELECT COUNT(*) FROM student3 WHERE age>40

SELECT SUM(math) FROM student3 

SELECT AVG(math) FROM student3

SELECT MAX(math) FROM student3

SELECT MIN(math) FROM student3

INSERT INTO student3(id,NAME,age,sex,address,math,english) 
VALUES (9,'唐僧',25,'男','长安',87,78), 
(10,'孙悟空',18,'男','花果山',100,66), 
(11,'猪八戒',22,'男','高老庄',58,78), 
(12,'沙僧',50,'男','流沙河',77,88), 
(13,'白骨精',22,'女','白虎岭',66,66), 
(14,'蜘蛛精',23,'女','盘丝洞',88,88)

SELECT * FROM student3 LIMIT 2,6

SELECT * FROM student3 LIMIT 0,5

SELECT * FROM student3 LIMIT 5

主键

CREATE TABLE st5(
id INT PRIMARY KEY,
NAME VARCHAR(20),
age INT
)

INSERT INTO st5 (id, NAME) VALUES (1, '唐伯虎'),(2, '周文宾'),(3, '祝枝山'),(4, '文征明')
INSERT INTO st5 (id, NAME) VALUES (1, '文征明2')
INSERT INTO st5 (id, NAME) VALUES (NULL, '文征明3')

ALTER TABLE st5 DROP PRIMARY KEY

主键自增

CREATE TABLE st6(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT
)

INSERT INTO st6(NAME,age) VALUES('唐僧',22)
INSERT INTO st6(NAME,age) VALUES('孙悟空',26)
INSERT INTO st6 (NAME, age) VALUES ('猪八戒', 25)
INSERT INTO st6 (NAME, age) VALUES ('沙僧', 20)

外键和级联

CREATE TABLE department ( 
id INT PRIMARY KEY AUTO_INCREMENT, 
dep_name VARCHAR(20), 
dep_location VARCHAR(20) 
)

CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_id INT,
CONSTRAINT employee_depid_ref_department_id_fk FOREIGN KEY(dep_id) REFERENCES
 department(id) ON UPDATE CASCADE ON DELETE CASCADE
 )

INSERT INTO department (dep_name, dep_location) 
VALUES ('研发部', '广州'), ('销售部', '深圳')

INSERT INTO employee (NAME, age, dep_id) 
VALUES ('张三', 20, 1), ('李四', 21, 1), 
('王五', 20, 1), ('老王', 20, 2), 
('大王', 22, 2), ('小王', 18, 2)

INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5)
INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 1)

INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1),
('李四', 21, 1),('王五', 20, 1),('老王', 20, 2),('大王', 22, 2),('小王', 18, 2)

UPDATE department SET id=10 WHERE id=1

DELETE FROM department WHERE id=2

多表查询：内外连接

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

CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
NAME VARCHAR(20),
gender CHAR(1),
salary DOUBLE,
join_date DATE,
dept_id INT,
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept(id) ON DELETE CASCADE ON UPDATE CASCADE
)

SELECT * FROM emp, dept WHERE emp.`dept_id`=dept.`id`

SELECT * FROM emp INNER JOIN dept ON emp.`dept_id`=dept.`id`

SELECT * FROM emp LEFT OUTER JOIN dept ON emp.`dept_id`=dept.`id` AND emp.`gender`='男'

SELECT * FROM dept RIGHT OUTER JOIN emp ON emp.`dept_id`=dept.`id` AND emp.`gender`='男'