Mysql 基础教程
1. MySQL 简介
1.1 关系型数据库
MySQL
sqllite
db2
oracle
access
sql server
PostgreSQL
...
1.2 非关系型数据库
redis
mongodb
...
2. MySQL 安装
2.1 Windows
- https://dev.mysql.com/downloads/mysql/
- 下载完成之后解压
- 在与 bin 目录同级创建 data 文件夹
- 在 bin 目录下找到
mysql.exe和mysqld.exe - 在 bin 目录下打开 命令行窗口
- 输入命令
mysqld --initialise-insecure// 服务端初始化 - 这时查看 data 文件夹中是否已经创建了新的文件夹和文件
- 如果成功创建了，则表示安装成功 并且 mysql 还自动创建了一个用户 荣户名为
root密码为空 - 启动服务端：在 bin 目录下打开命令行窗口 输入
mysqld命令并执行 - 重新打开一个命令行窗口 输入
mysql -uroot -p点击回车，这是命令行提示输入密码，直接点击回车就可登录成功了 - 在当前命令行窗口 输入
show databases;查看 mysql 帮我们创建的文件夹(数据库) - 再次输入命令
create database test_db;再 data 目录下创建一个test_db目录(数据库)
2.2 制作全局mysql服务
- 在 bin 目录下打开命令行窗口 输入
mysqld -install命令并执行 此电脑->右键属性->高级系统设置->环境变量->系统环境变量->Path->编辑->新建->将安装mysql bin 目录添加到Path 中->确定此电脑->右键管理->双击服务和应用程序->双击服务->找到MySQL->点击启动net start MySQL启动MySQL服务net stop MySQL停止MySQL服务
2.3 MySQL创建用户以及授权
-
创建用户
语法 create user '用户名'@'IP' identified by '密码'; create user 'test_user'@'192.168.1.1' identified by '123456'; // 这样创建的用户只能在192.168.1.1 这台机器上使用 create user 'test_user'@'%' identified by '123456'; // 这样创建的用户所有机器上都可以登录 -
删除用户
语法: drop user '用户名'@'IP地址'; drop user 'test_user'@'192.168.1.1'; drop user 'test_user'@'%'; -
修改用户名
语法: rename user '旧用户名'@'IP地址' to '新用户名'@'IP地址'; -
修改用户密码
语法: set password for '旧用户名'@'IP地址' = password('新密码'); -
查看用户权限
语法: show grant for '用户'@'IP地址'; -
授权
语法：grant 权限 on 数据库名.表名 grant select,insert on test_db.test_table to 'test_user'@'%' ; // 这就表示 test_user 可以在任意机器上对 test_db.test_table 表进行 读取和插入操作 grant all privileges on test_db.test_table to 'test_user'@'%' ; // 这就表示 test_user 可以在任意机器上对 test_db.test_table 表进行 任意操作（除了授权） grant all privileges on test_db.* to 'test_user'@'%'; // 这就表示 test_user 可以在任意机器上对 test_db 中的所有表进行 任意操作（除了授权） -
取消权限
语法: revoke 权限 on 数据库.表名 from '用户'@'IP地址';
3. MySQL规则
3.1 操作数据库
-
创建数据库
语法：create database 数据库名; create database my_db; // 最简单的方式，只创建数据库 (不推荐) create database my_db default character set utf8; // 创建数据库并设定编码格式 (推荐) create database if not exists my_db default character set utf8; // 判断原来不存在，则创建数据库，并指定编码格式 (推荐) create database if not exists my_db default character set utf8 collate utf8_general_ci; // 判断原来不存在，则创建数据库，并指定编码格式和校验集 (推荐) -
查看数据库
show databases; -
删除数据库
drop database my_db; -
选择数据库
use my_db;
3.2 操作数据表
-
查看数据表
1 显示所有数据表：show tables; 2 显示表结构：desc user; 3 显示数据表是如何创建的：show create table user; 4 显示数据表是如何创建的：show create table user G; -
创建数据表
- 语法：CREATE TABLE
表名(字段1类型1,字段2类型2,字段3类型3)ENGINE=引擎 DEFAULT CHARSET=字符编码; - 实例：
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(128) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 分析
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(128) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
// NULL 表示该字段可以为空 // NOT NULL 表示该字段不能为空 // AUTO_INCREMENT 表示自增 // DEFAULT NULL 表示默认值为NULL // PRIMARY KEY 表示主键(不能相同且不能为空，可加速查找，一张表中只能有一个主键) // ENGINE=InnoDB 指定引擎类型为InnoDB，至此事务，原子性操作 // CHARSET=utf8 指定字符编码
- 语法：CREATE TABLE
-
修改数据表
- 修改自增列的初始值：alter table user AUTO_INCREMENT=10;
- 设置自增列步长：基于会话去做的
# 查看全局步长 show session variables like 'auto_inc%'; # 设置会话步长 set session auto_increment_increment=2; # 设置起始值 set session auto_increment_offset=10;
- 设置自增列步长：基于全局去做的(不推荐)
shwo global variables like 'auto_inc%'; set global auto_increment_increment=2; set global auto_increment_offset=10;
3.3 操作数据
-
查看数据
语法：select 字段1,字段2,字段3 from 表名; SELECT id,name,age FROM user; SELECT * FROM user; -
添加数据
- 插入单条数据
INSERT INTO user (id,name,age) VALUES (1,'cainiao',18);
- 插入数据时 会出现中文乱码的问题，这里我使用的是 Navcat 插入中文（网上的那些回答，不一定会解决这个问题，我尝试了一下午最后还是妥协了）
- 插入多条数据
INSERT INTO `user` (`id`, `name`, `age`) VALUES ('1', '菜鸟程序员','18'), ('2', '张三', '20'), ('3', '李四', '18'), ('4', '王麻子', '19'), ('5', '翠花', '21');
- 插入单条数据
-
修改数据
UPDATE user SET id='1', name='菜鸟程序员_python', age='18' WHERE (id='1');
-
删除数据
DELETE FROM user; // 删除表中的全部数据（下次插入的时候，id由之前删除的开始） TRUNCATE table user; // 删除表中所有数据（下次插入的时候，id由1开始，推荐使用这种方式是，速度快）
3.4 MySQL基本数据类型：
-
数字类型 (unsigned关键字,指定无符号)
-
tinyint: (小整数值)
- 大小 : 1 字节
- 范围 : (-128,127) | (0,255)
- 实例 : num tinyint(1),
-
smallint: (大整数值)
- 大小 : 2 字节
- 范围 : (-32 768，32 767) | (0，65 535)
- 实例 : num smallint(6),
-
mediumint: (大整数值)
- 大小 : 3 字节
- 范围 : (-8 388 608，8 388 607) | (0，16 777 215)
- 实例 : num mediumint(8),
-
int/integer: (大整数值)
- 大小 : 4 字节
- 范围 : (-2 147 483 648，2 147 483 647) | (0，4 294 967 295)
- 实例 : num int(11),
-
bigint: (极大整数值)
- 大小 : 8 字节
- 范围 : (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615)
- 实例 : num bigint(20),
-
float: (单精度浮点数值)
- 大小 : 4 字节
- 范围 : (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38)
- 实例 : num float,
-
double: (双精度浮点数值)
- 大小 : 8 字节
- 范围 : (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
- 实例 : num double,
-
decimal: (非常精准)
- 实例 : num decimal(10,8), // 表示小数点前可以有10位，小数点后可以有8位
-
-
字符串类型
-
char: (定长字符串 查询速度快)
- 大小 : 0-255字节
- 实例 : name char(255),
-
varchar: (变长字符串 节省空间,查询速度慢)
- 大小 : 0-65535 字节
- 实例 : name varchar(255),
-
tinyblob: (不超过 255 个字符的二进制字符串)
- 大小 : 0-255字节
- 实例 : name tinyblob(255),
-
tinytext: (短文本字符串)
- 大小 : 0-255字节
- 实例 : name tinytext(255),
-
blob: (二进制形式的长文本数据)
- 大小 : 0-65 535字节
- 实例 : image blob,
-
text: (长文本数据)
- 大小 : 0-65 535字节
- 实例 : name text,
-
mediumblob: (二进制形式的中等长度文本数据)
- 大小 : 0-16 777 215字节
- 实例 : image mediumblob,
-
mediumtext: (中等长度文本数据)
- 大小 : 0-16 777 215字节
- 实例 : name mediumtext,
-
longblob: (二进制形式的极大文本数据)
- 大小 : 0-4 294 967 295字节
- 实例 : video longblob,
-
longtext: (极大文本数据)
- 大小 : 0-4 294 967 295字节
- 实例 : name longtext,
-
-
时间类型
-
date: (日期值)
- 大小 : 3字节
- 范围 : 1000-01-01/9999-12-31
- 格式 : YYYY-MM-DD
- 实例 : add_time date,
-
time: (时间值或持续时间)
- 大小 : 3字节
- 范围 : '-838:59:59'/'838:59:59'
- 格式 : HH:MM:SS
- 实例 : add_time time,
-
year: (年份值)
- 大小 : 1字节
- 范围 : 1901/2155
- 格式 : YYYY
- 实例 : add_time year,
-
datetime: (混合日期和时间值)
- 大小 : 8字节
- 范围 : 1000-01-01 00:00:00/9999-12-31 23:59:59
- 格式 : YYYY-MM-DD HH:MM:SS
- 实例 : add_time datetime,
-
timestamp: (混合日期和时间值，时间戳)
- 大小 : 4字节
- 范围 : 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间2038年1月19日 凌晨 03:14:07
- 格式 : YYYYMMDD HHMMSS
- 实例 : add_time timestamp,
-
-
其他类型
-
enum: (枚举类型)
- 实例1 :
CREATE TABLE my_enum1 ( name varchar(128), color enum('red','green','blue') ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO my_enum1 (name,color) VALUES ('张三','red'),('MySQL','green');
- 实例2 :
CREATE TABLE my_enum2 ( name varchar(128), size enum('x-small','small','medium','large','x-large') ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO my_enum2 (name,size) VALUES ('李四','medium');
- 实例3 :
CREATE TABLE my_enum3 ( name varchar(128), gender enum('男','女','中性') ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO my_enum3 (name,gender) VALUES ('麻子','男');
- 实例1 :
-
set: (集合类型)
- 实例：
CREATE TABLE myset ( col SET('a', 'b', 'c', 'd') ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
- 实例：
-
4. MySQL基本增删改查
4.1 增
语法 : INSERT INTO 表名(字段名1,字段名2,字段名3,...) VALUES (值1,值2,值3,...);
实例1 : INSERT INTO user(id,name,age) VALUES (1,'张三',20);
实例2 : INSERT INTO user(id,name,age) VALUES (1,'张三',20),(2,'李四',28),(3,'麻子',58),(4,'翠花',18);
4.2 删
语法 : DELETE FROM 表名 WHERE 条件; // 不加条件表示全部删除
实例1 : DELETE FROM user WHERE age < 18;
实例2 : DELETE FROM user;
实例3 : TRUNCATE table user;
实例4 : TRUNCATE table user WHERE age < 18;
4.3 改
语法 : UPDATE 表名 SET 字段名1=值1, 字段名2=值2 WHERE 条件; // 不加条件表示全部修改
实例1 : UPDATE user SET age=19 WHERE id < 5;
实例2 : UPDATE user SET age=20;
4.4 查
语法 : SELECT 字段1,字段2,字段3 FROM 表名 WHERE 条件; // 不加条件表示查询全部
实例1 : SELECT id,name,age From user;
实例2 : SELECT * FROM user;
实例3 : SELECT name,age FROM user WHERE age > 18;
5. 外键&主键&索引
5.1 主键
- 主键关键字 : PRIMARY KEY
- 简介 : 一张表中只能有一个主键，且不能为空,可以加速查找
- 实例 :
CREATE TABLE user ( id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(128), ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
5.2 外键
-
需求：现在要创建一张商品表
- 创建表
CREATE TABLE goods ( # 商品主键自增 id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, # 商品名称 name varchar(128) NOT NULL, # 商品价格 price double NOT NULL, # 库存 stock int(11) DEFAULT 10000, # 商品分类 goods_category enum('水果','甜点','蔬菜') ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 插入数据
INSERT INTO goods (name, price,stock,goods_category) VALUES ('草莓', '19.99', 2000, '水果'), ('香蕉', '3.88', 1000, '水果'), ('蛋挞', '9.99', 900, '甜点'), ('西红柿', '2.99', 5000, '蔬菜'), ('土豆', '3.98', 8000, '蔬菜');
- 问题：现在我想添加一条数据,牛肉,牛肉属于肉类,但是enum('水果','甜点','蔬菜')中没有肉类这个分类
- 创建表
-
完成需求: 解决问题
-
创建商品分类表
CREATE TABLE goods_category ( # 商品分类主键自增 id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, # 商品分类标题 title VARCHAR (128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
创建商品表
CREATE TABLE goods ( # 商品主键自增 id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, # 商品名称 name VARCHAR (128) NOT NULL, # 商品价格 price DOUBLE NOT NULL, # 库存 stock INT (11) DEFAULT 10000, # 商品分类 goods_category_id INT (11), # 外键 CONSTRAINT fk_goods_goodscategory FOREIGN KEY (goods_category_id) REFERENCES goods_category(id) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
-
插入数据
# 商品分类 INSERT INTO goods_category (id, title) VALUES (1, '水果'), (2, '甜点'), (3, '蔬菜'), (4, '肉类'), (5, '海鲜'); # 商品 INSERT INTO goods (name, price,stock,goods_category_id) VALUES ('草莓', '19.99', 2000, 1), ('香蕉', '3.88', 1000, 1), ('蛋挞', '9.99', 900, 2), ('西红柿', '2.99', 5000, 3), ('土豆', '3.98', 8000, 3), ('牛肉', '49.98', 8000, 4), ('海参', '53.98', 8000, 5);
-
-
拓展：
CREATE TABLE db1 ( id_1 INT (11) NOT NULL AUTO_INCREMENT, id_2 INT (11) NOT NULL, name VARCHAR (128) NOT NULL, PRIMARY KEY(id_1,id_2) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE db2 ( name VARCHAR (128) NOT NULL db1_id_1 INT (11), db1_id_2 INT (11), CONSTRAINT fk_db1_db2 FOREIGN KEY (db1_id_1,db1_id_2) REFERENCES db1(id_1,id_2) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # 一个表只能有一个主键，主键可以由多列组成 # 注意：外键名"必须唯一" # 这种方式不常用但是还是要知道
5.3 练习
-
创建表
-
创建老师表
CREATE TABLE teachers ( id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR (128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
创建班级表
CREATE TABLE class ( id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, caption VARCHAR (128) NOT NULL, teacher_id INT (11), CONSTRAINT fk_class_teacher FOREIGN KEY (teacher_id) REFERENCES teachers (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
-
创建学生表
CREATE TABLE students ( id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR (128) NOT NULL, age INT (11), gender enum ('男', '女', '中性'), class_id INT (11), CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
-
创建课程表
CREATE TABLE courses ( id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR (128) NOT NULL, teacher_id INT (11), CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teachers (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
-
创建成绩表
CREATE TABLE scores ( id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, student_id INT (11), courser_id INT (11), number INT (3), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES students (id), CONSTRAINT fk_score_courser FOREIGN KEY (courser_id) REFERENCES courses (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
-
-
插入数据
-
老师数据插入
INSERT INTO teachers (id, name) VALUES (1, '姜子牙'), (2, '老夫子'), (3, '狄仁杰'), (4, '明世隐'), (5, '女娲');
-
班级数据插入
INSERT INTO class (id, caption, teacher_id) VALUES (1, '一年级一班',1), (2, '一年级二班',2), (3, '二年级一班',2), (4, '二年级二班',1), (5, '三年级一班',3), (6, '三年级二班',3), (7, '四年级一班',5), (8, '四年级二班',4), (9, '五年级一班',5), (10, '五年级二班',5);
-
学生数据插入
INSERT INTO students (id, name, age, gender, class_id) VALUES (1, '孙尚香', 7, '女', 1), (2, '刘备', 8, '男', 1), (3, '赵云', 9, '男', 3), (4, '张飞', 10, '男', 4), (5, '关羽', 11, '男', 6), (6, '程咬金', 12, '男', 6), (7, '妲己', 13, '男', 7), (8, '嬴政', 14, '男', 8), (9, '小乔', 15, '男', 9), (10, '廉颇', 16, '男', 9);
-
课程数据插入
INSERT INTO courses (id, name,teacher_id) VALUES (1, '语文', 1), (2, '数学', 2), (3, '英语', 3), (4, '生物', 4), (5, '计算机', 5);
-
成绩数据插入
INSERT INTO scores (id, student_id, courser_id, number) VALUES (1, 1, 1, 80), (2, 3, 2, 56), (3, 5, 3, 43), (4, 7, 4, 99), (5, 9, 5, 100);
-
5.4 索引
-
唯一索引
CREATE TABLE user ( id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, mbile INT (11) NOT NULL, UNIQUE only_mbile (mbile) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -
联合索引
CREATE TABLE user ( id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, mbile INT (11) NOT NULL, mailbox VARCHAR (128) NOT NULL, UNIQUE only_mbile_mailbox (mbile,mailbox) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -
PS：
# 唯一索引 约束不能重复（可以为空） # 主键 主键不能重复（不能为空） # 加速查找
5.5 外键的变种：
-
用户表&博客园表(一对一)
-
设计
-
|
user 用户表 |
|||
|
id |
name |
age |
gender |
|
1 |
孙尚香 |
7 |
女 |
|
2 |
刘备 |
8 |
男 |
|
3 |
赵云 |
9 |
男 |
|
4 |
张飞 |
10 |
男 |
|
5 |
关羽 |
11 |
男 |
|
6 |
程咬金 |
12 |
男 |
|
7 |
妲己 |
13 |
女 |
|
8 |
嬴政 |
14 |
男 |
|
9 |
小乔 |
15 |
女 |
|
10 |
廉颇 |
16 |
男 |
|
blogs-博客表 |
||
|
id |
suffix |
userid |
|
1 |
/zhaoyun/ |
3 |
|
2 |
/zhangfei/ |
4 |
|
3 |
/guanyu/ |
5 |
-
- 实例
# 用户表
CREATE TABLE user (
id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR (128) NOT NULL,
age INT (11),
gender enum ('男', '女', '中性'),
) ENGINE = INNODB DEFAULT CHARSET = utf8;
# 博客表
CREATE TABLE blogs (
id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
suffix VARCHAR (128) NOT NULL,
user_id int unique,
constraint fk_user_blogs foreign key(user_id) references user(id)
);
-
商品分类表&商品表（一对多）
-
设计
-
|
goods_category 商品分类表 |
|
|
id |
title |
|
1 |
水果 |
|
2 |
甜点 |
|
3 |
蔬菜 |
|
4 |
肉类 |
|
5 |
海鲜 |
|
goods 商品表 |
||||
|
id |
name |
price |
stock |
category |
|
1 |
草莓 |
19.99 |
2000 |
1 |
|
2 |
香蕉 |
3.88 |
1000 |
1 |
|
3 |
蛋挞 |
9.99 |
9000 |
2 |
|
4 |
西红柿 |
2.99 |
5000 |
3 |
|
5 |
土豆 |
3.98 |
8000 |
3 |
|
6 |
牛肉 |
49.99 |
500 |
4 |
|
7 |
海参 |
59.88 |
200 |
5 |
-
-
实例
# 商品分类表 CREATE TABLE goods_category ( id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, title VARCHAR (128) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # 商品表 CREATE TABLE goods ( # 商品主键自增 id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR (128) NOT NULL, price DOUBLE NOT NULL, stock INT (11) DEFAULT 10000, goods_category_id INT (11), CONSTRAINT fk_goods_goodscategory FOREIGN KEY (goods_category_id) REFERENCES goods_category(id) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
-
-
商品表&用户表&购物车（多对多）
-
设计
一个用户可以买多个商品，一个商品可以多个用户购买goods 商品表
id
name
price
stock
category
1
草莓
19.99
2000
1
2
香蕉
3.88
1000
1
3
蛋挞
9.99
9000
2
4
西红柿
2.99
5000
3
5
土豆
3.98
8000
3
6
牛肉
49.99
500
4
7
海参
59.88
200
5
user 用户表
id
name
age
gender
1
孙尚香
7
女
2
刘备
8
男
3
赵云
9
男
4
张飞
10
男
5
关羽
11
男
6
程咬金
12
男
7
妲己
13
女
8
嬴政
14
男
9
小乔
15
女
10
廉颇
16
男
shopping_cart 购物车表
id
goods_id
user_id
number
1
1
1
1
2
1
2
2
3
2
5
2
4
3
10
1
5
4
8
3
6
5
6
1
7
5
7
4
8
4
9
1
9
6
1
3
100
7
9
1
-
实例
# 商品表 CREATE TABLE goods ( # 商品主键自增 id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR (128) NOT NULL, price DOUBLE NOT NULL, stock INT (11) DEFAULT 10000 ) ENGINE = INNODB DEFAULT CHARSET = utf8; # 用户表 CREATE TABLE user ( id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR (128) NOT NULL, age INT (11), gender enum ('男', '女', '中性') ) ENGINE = INNODB DEFAULT CHARSET = utf8; # 购物车 CREATE TABLE shopping_cart ( id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT, goods_id INT (11), user_id INT (11), number INT (11) DEFAULT 1, CONSTRAINT fk_goods_id FOREIGN KEY (goods_id) REFERENCES goods(id), CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES user(id) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
-
6. SQL语句详细介绍
6.0 数据表
# 用户表1
CREATE TABLE user (
id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR (128) NOT NULL,
age INT (11),
gender enum ('男', '女', '中性')
) ENGINE = INNODB DEFAULT CHARSET = utf8;
# 用户表2
CREATE TABLE user2 (
id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR (128) NOT NULL,
age INT (11),
gender enum ('男', '女', '中性')
) ENGINE = INNODB DEFAULT CHARSET = utf8;
# 商品分类表
CREATE TABLE goods_category (
# 商品分类主键自增
id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
# 商品分类标题
title VARCHAR (128) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 商品表
CREATE TABLE goods (
# 商品主键自增
id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
# 商品名称
name VARCHAR (128) NOT NULL,
# 商品价格
price DOUBLE NOT NULL,
# 库存
stock INT (11) DEFAULT 10000,
# 商品分类
goods_category_id INT (11),
# 外键
CONSTRAINT fk_goods_goodscategory FOREIGN KEY (goods_category_id) REFERENCES goods_category(id)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
# 数据自己插入
6.1 增
INSERT INTO user (name,age,gender) VALUES ('张三',18,'男'); # 添加单条数据到user表中
INSERT INTO user (name,age,gender) VALUES ('翠花',18,'女'), ('李四',28,'男'), ('王麻子',38,'男'); # 添加单条数据到user表中
INSERT INTO user2 (name,age,gender) SELECT name,age,gender FROM user; # 将user表中的数据全部添加到user2表中
6.2 删
DELETE FROM user; # 删除user表中的所有数据（保留id）
DELETE FROM user WHERE id = 1; # 删除user表中id等于1的所有用户
DELETE FROM user WHERE id > 3; # 删除user表中id大于3的所有用户
DELETE FROM user WHERE age >= 20; # 删除user表中age大于等于20的所有用户
DELETE FROM user WHERE age >= 20 and name = '张三'; # 删除user表中age大于等于20 并且name等于张三的所有用户
DELETE FROM user WHERE age >= 20 or name = '张三'; # 删除user表中age大于等于20 或者name等于张三的所有用户
TRUNCATE table user; # 删除user表中的所有数据（不保留id）
TRUNCATE table user WHERE id = 2; # 删除user表中id等于2的所有用户
TRUNCATE table user user WHERE id > 3; # 删除user表中id大于3的所有用户
TRUNCATE table user WHERE age >= 20; # 删除user表中age大于等于20的所有用户
TRUNCATE table user WHERE age >= 20 and name = '张三'; # 删除user表中age大于等于20 并且name等于张三的所有用户
TRUNCATE table user WHERE age >= 20 or name = '张三'; # 删除user表中age大于等于20 或者name等于张三的所有用户
6.3 改
UPDATE user SET age=20 WHERE id = 1; # 将id等于1的所有用户age改为20
UPDATE user SET age=20 WHERE id > 3; # 将id大于3的所有用户age改为20
UPDATE user SET age=20 WHERE age >= 20; # 将age大于等于20的所有用户age改为20
UPDATE user SET age=20 WHERE age >= 20 and name = '张三'; # 将age大于等于20,并且name等于张三的age全都改为20
UPDATE user SET age=20 WHERE age >= 20 or name = '张三'; # 将age大于等于20,或者name等于张三的age全都改为20
UPDATE user SET name='菜鸟程序员',age=20 WHERE id = 1; # 将id等于1的所有用户,age全都改为20,name改为菜鸟程序员
6.4 查（***）
-
基础查询
SELECT * FROM user; # 查看user表中的所有用户信息 SELECT name,age FROM user; # 查看user表中的所有用户name,age信息 -
WHERE查询(条件查询)
SELECT * FROM user WHERE age != 18; # 查看user表中age不等于18的所有用户信息 SELECT * FROM user WHERE id in (1,5,8); # 查看user表中 id=1 或 id=5 或 id=8 的所有用户信息 SELECT * FROM user WHERE age not in (20,30,40); # 查看user表中 age!=20 或 age!=30 或 age!=40 的所有用户信息 SELECT * FROM user WHERE id between 3 and 5; # 查看user表中 id>=3 id<=5 的所有用户信息 SELECT name,age FROM user WHERE id > 10 and age <= 18; # 查看user表中id大于10,并且age小于等于18的所有用户name,age信息 SELECT name,age FROM user WHERE id > 10 or age <= 18; # 查看user表中id大于10,或者age小于等于18的所有用户name,age信息 SELECT * FROM user WHERE id in (SELECT id FROM user2) -
LIKE(通配符)
SELECT * FROM user WHERE name LIKE '张%'; # 张开头的所有（张后边可以有任意多个字符串） SELECT * FROM user WHERE name LIKE '%张'; # 张结尾的所有（张前边可以有任意多个字符串） SELECT * FROM user WHERE name LIKE '张_'; # 张开头的所有（张后边只能有一个字符） SELECT * FROM user WHERE name LIKE '张_'; # 张结尾的所有（张前边只能有一个字符） SELECT * FROM user WHERE name LIKE '%张%'; # 只要包含张的所有 -
LIMIT(分页,限制)
SELECT * FROM user LIMIT 5; # 前5行 SELECT * FROM user LIMIT 4,5; # 从第4行开始的5行 SELECT * FROM user LIMIT 5 OFFSET 4; # 从第4行开始的5行 -
ORDER BY(排序)
SELECT * FROM user ORDER BY id ASC; # 根据 id 从小到大排列 SELECT * FROM user ORDER BY id DESC; # 根据 id 从大到小排列 SELECT * FROM user ORDER BY age DESC,id ASC; # 根据 age 从大到小排列，如果相同则按 id 从小到大排序 SELECT * FROM user ORDER BY id DESC LIMIT 5; # 根据 id 从大到小排列,拿前五条数据 -
GROUP BY(分组)
SELECT * FROM goods GROUP BY goods_category_id; # 查询所有的商品，去除商品类型相同的数据 SELECT name,price FROM goods GROUP BY price,goods_category_id; SELECT name,price FROM goods WHERE price > 10 GROUP BY goods_category_id ORDER BY price DESC; SELECT name,price,COUNT(*),SUM(price),MAX(price),MIN(price) FROM goods GROUP BY goods_category_id; SELECT * FROM goods GROUP BY goods_category_id HAVING COUNT(id)>1; # 对聚合函数进行二次筛选必须使用HAVING -
JOIN(连表查询)
SELECT * FROM goods,goods_category WHERE goods.goods_category_id = goods_category.id; SELECT * FROM goods LEFT JOIN goods_category ON goods.goods_category_id=goods_category.id; # LEFT JOIN 表示goods表中的数据会全部显示 SELECT * FROM goods RIGHT JOIN goods_category ON goods.goods_category_id=goods_category.id; # LEFT JOIN 表示goods_category表中的数据会全部显示 SELECT * FROM goods INNER JOIN goods_category ON goods.goods_category_id=goods_category.id; # INNER JOIN 表示无对应关系则不显示 -
union(组合查询)
select nickname from A union select name from B # 组合，自动处理重合 select nickname from A union all select name from B # 组合，不处理重合 -
其他
SELECT name as cname,age FROM user; # 查看user表中的所有用户信息,并将name列的列名该为cname SELECT name,age,11 FROM user; # 查看user表中的所有用户信息,并添加一列，列名为11，值为11