本文实验用数据库为MySQL 8
1.创建数据库(CREATE DB)
- 语法
CREATE DATABASE database_name
- 实例
CREATE DATABASE school
2.创建数据库表(CREATE Table)
- 语法
CREATE TABLE 表名称 ( 列名称1 数据类型, 列名称2 数据类型, 列名称3 数据类型, .... )
- 实例
CREATE TABLE student ( id bigint NOT NULL, name varchar(255), sex varchar(255), age int, PRIMARY KEY (id) )
3.修改数据结构(ALTER TABLE)
- 语法
1.添加列(ADD)
ALTER TABLE table_name ADD column_name datatype
2.删除列(DROP COLUMN)
ALTER TABLE table_name DROP COLUMN column_name
3.修改列(MODIFY COLUMN)
ALTER TABLE table_name MODIFY COLUMN column_name datatype
- 实例
1.在student表中添加class_id列
2.在student表中修改class_id列数据类型为varchar字符串类型ALTER TABLE student ADD class_id INT
3.在student表中删除class_id列ALTER TABLE student MODIFY COLUMN class_id VARCHAR(255)
ALTER TABLE student DROP COLUMN class_id
4.插入数据(INSERT INTO)
- 语法
INSERT INTO 表名称 VALUES (值1, 值2,....)
或
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
- 实例
INSERT INTO student VALUES (1,'张三','男',18); INSERT INTO student (id, name,age) VALUES (2, '李四',16); INSERT INTO student (id, name,sex) VALUES (3, '刘翠花','女'); INSERT INTO student (id, name,sex,age) VALUES (4, '张小红','女',15);
5.删除数据(DELETE)
- 语法
1.按条件删除
DELETE FROM 表名称 WHERE 列名称 = 值
2.删除所有
DELETE FROM table_name
或
DELETE * FROM table_name
- 实例
delete from student where id = 4
6.更新数据(UPDATE)
- 语法
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
- 实例
UPDATE student SET sex = '男' WHERE id = 2; update student set age=17 where name = '刘翠花'
7.查询数据(SELECT)
-
语法
SELECT 列名称 FROM 表名称
或
SELECT * FROM 表名称
-
实例1
SELECT * FROM student
-
结果
-
实例2
SELECT name, sex FROM student
-
结果
8.条件查询(WHERE)
- 语法
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
- 实例
SELECT * FROM student where sex = '男'
9.模糊查询(LIKE)
- 实例
SELECT * FROM student where name LIKE '%张%'
- 备注
'%张%‘表示字符串中有’张’;
'张%‘表示字符串开头为’张’;
‘%张’表示字符串结尾为’张’;
10.多条件查询(AND & OR)
-
语法
SELECT 列名称 FROM 表名称 WHERE 条件1 AND 条件2
SELECT 列名称 FROM 表名称 WHERE 条件1 OR 条件2
SELECT 列名称 FROM 表名称 WHERE (条件1 AND 条件2) OR 条件3
-
实例
select name, age from student where (name like '%张%' AND age > 17) OR sex= '女'
11.去重查询(DISTINCT)
- 语法
SELECT DISTINCT 列名称 FROM 表名称
- 实例
select DISTINCT sex from student
12.排序查询(ORDER BY)
- 实例(升序)
select * from student ORDER BY age
- 实例(降序)
select * from student where sex = '男' ORDER BY age DESC
- 实例(按姓名拼音字母顺序排序,并在姓名相同时按年龄降序排序)
select * from student where sex = '男' ORDER BY name DESC, age ASC
- 备注
ASC:默认值,升序
DESC:降序
按汉字排序的规则:拼音+四声调排序,默认倒序,DESC正序(a-z + 一声到四声)
13.分页查询(LIMIT)
- 语法
SELECT column_name(s) FROM table_name LIMIT number
- 实例
1.查询student表中前两条数据
2.按年龄正序排序并查询出第2-3条数据SELECT * from student LIMIT 2
SELECT * from student ORDER BY age LIMIT 1, 2
- 解释
LIMIT后有两个参数时,如LIMIT m, n
表示查询出第m条数据(不包含第m条)之后的n条数据;
当n=-1,即LIMIT m, -1
;表示第m条数据(不包含第m条)之后的所有数据;
LIMIT后只有一个参数时,如LIMIT n
表示查询出前n条数据,等同于LIMIT 0, n
14.查询条件值范围 (IN)
- 语法
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
- 实例
SELECT * from student WHERE age IN (16,17)
15.查询条件值区间 (BETWEEN)
- 语法
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
- 实例
SELECT * from student WHERE age BETWEEN 16 AND 17
下面的实验需要用到多个表,现在我们把student表和class表准备好,表结构及表内容如下
student表结构
class表结构
student表数据
class表数据
16.别名(AS)
-
语法
1.列别名
SELECT column_name AS alias_name FROM table_name
2.表别名
SELECT column_name(s) FROM table_name AS alias_name
-
实例
1.列别名实例SELECT id as number ,name as full_name from student
2.表别名实例
SELECT c.class_name ,s.name FROM class AS c ,student AS s WHERE s.class_id = c.id and c.class_name = '高三·一班'
3.列别名+表别名实例
SELECT c.class_name AS class,s.name as student FROM class AS c ,student AS s WHERE s.class_id = c.id and c.class_name = '高三·一班'
17.连接(JOIN)
- 语法
1.内连接(INNER JOIN)
SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name
2.左连接(LEFT JOIN)
SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
3.右连接(RIGHT JOIN)
SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
4.全连接(FULL JOIN)
SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name
MySQL数据库不支持FULL JOIN用法,要想达到与FULL JOIN相同的效果可以用UNION实现,如下SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name UNION SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name