一:SQL结构化查询语言分类
DDL:数据定义语言
DCL:数据控制语言
DML:数据操作语言
DQL:数据检索语言
二:DDL数据定义语言
2.1 库定义
2.1.1 创建库
mysql> create database cxytest charset utf8mb4;
2.1.2 删除数据库(生产中禁止使用)
mysql> drop database cxytest;
2.1.3 修改字符集
mysql> alter database test charset utf8mb4;
2.1.4 查看创建的数据库
mysql> show create database cxytest;
+----------+---------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------+
| cxytest | CREATE DATABASE `cxytest` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
2.2 表定义
2.2.1 列属性
数据类型
数字类型:
tinyint 1个字节 0-255 3位数
int 3个字节 0-4294967295 10位数
说明:手机号码是无法存储到int的,一般是使用char类型来存储手机号
字符类型:
char(10)
定长10位,存储空间1次性分配,如果字符没有写满10位,也分配10位,可以分配255个;char(255)
varchar(10)
可变长10位,按需分配空间,写几位占几位,可以分配65535个;char(65535)
enum('m','f') 枚举类型
比较适合于将来此列的值是固定范围内的特点,可以使用enum,可以很大程度的优化我们的索引结构
时间类型:
datetime
时间范围从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999
timestamp
时间范围从1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999
约束
primary key 主键:非空,唯一
unique key:唯一键;不重复
not null:非空
unsigned:无符号,数字类型的列,非负数
其他属性
default 默认值
auto_increment 自增长
comment 注释
2.2.2 表属性
engine 引擎
charset 字符集
comment 注释
2.3 表创建
2.3.1 创建语法
语法:create table 表名(
列1 属性(数据类型,约束,其他属性)
列2 属性
列3 属性
)
2.3.2 规范建表的例子
USE test; """切换到要建表的数据库下
CREATE TABLE stu(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号', """int整型;非空;主键;自增长;注释"""
sname VARCHAR(20) NOT NULL COMMENT '姓名', """varchar字符串;非空;注释"""
sage TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄', """tinyint整型;unsigned:无符号,数字类型的列,非负数;非空;有默认值为0;注释"""
sgender ENUM('男','女','未知') NOT NULL DEFAULT '未知', """枚举类型; 非空,有默认值'未知' """
sfz CHAR(18) NOT NULL UNIQUE COMMENT '身份证', """char字符串定长18位;非空;unique:唯一键不重复;注释"""
intime TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间' """timestamp时间类型;非空;默认值为当前时间;注释"""
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表'; """表引擎innodb;字符集utf8mb4;注释"""
建表规范:
- 表名小写
- 不能是数字开头
- 注意字符集和存储引擎
- 选择合适的数据类型
- 每个列都要有注释
- 每个列设置为非空,无法保证非空的,用0来填充
2.3.3 删表(生产中禁止使用此命令)
mysql > drop table stu;
2.3.4 修改表
在stu表中添加qq列
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq号';
在sname后加微信列
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;
在id列前加一个新列num
ALTER TABLE stu ADD num INT NOT NULL COMMENT '数字' FIRST;
删除刚才添加的列(危险)
ALTER TABLE stu DROP num;
修改sname数据类型的属性
ALTER TABLE stu MODIFY sname VARCHAR(128) NOT NULL;
修改sgender改为列名sg char(3) not null default '未知'
ALTER TABLE stu CHANGE sgender sg CHAR(3) NOT NULL DEFAULT '未知'
2.3.5 查看表属性
查看表中有哪些列以及其属性
mysql> desc stu;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(128) | NO | | NULL | |
| wechat | varchar(64) | NO | UNI | NULL | |
| sage | tinyint(3) unsigned | NO | | 0 | |
| sg | char(3) | NO | | 未知 | |
| sfz | char(18) | NO | UNI | NULL | |
| intime | timestamp | NO | | CURRENT_TIMESTAMP | |
| qq | varchar(20) | NO | UNI | NULL | |
+--------+---------------------+------+-----+-------------------+----------------+
8 rows in set (0.00 sec)
查看数据库里有哪些表
mysql> show tables;
+-------------------+
| Tables_in_cxytest |
+-------------------+
| stu |
+-------------------+
1 row in set (0.00 sec)
查看创建表时的语句
mysql> show create table stu;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu | CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`sname` varchar(128) NOT NULL,
`wechat` varchar(64) NOT NULL COMMENT '微信号',
`sage` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`sg` char(3) NOT NULL DEFAULT '未知',
`sfz` char(18) NOT NULL COMMENT '身份证',
`intime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
`qq` varchar(20) NOT NULL COMMENT 'qq号',
PRIMARY KEY (`id`),
UNIQUE KEY `sfz` (`sfz`),
UNIQUE KEY `qq` (`qq`),
UNIQUE KEY `wechat` (`wechat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表'
三:DCL数据控制语言
grant
revoke
在mysql基础管理里有介绍
四:DML 对表中的数据进行增删改
4.1 增 insert
最标准的insert语句
INSERT INTO stu(id,sname,sage,sgender,sfz,intime)
VALUES
(1,'张三',20,'男',1111134454,NOW());
省事的写法
INSERT INTO stu
VALUES (2,'赵四',29,'女','8796554678',NOW());
针对性的录入
INSERT INTO stu(sname,sfz)
VALUES ("王铁柱",9808988332)
同时录入多行数据
INSERT INTO stu(sname,sage,sg,sfz,intime)
VALUES
('田二妞',29,'女','6549890',NOW()),
('支付宝',76,'男',4321908,NOW()),
('王大富',33,'未知','23213434',NOW());
4.2 删(谨慎使用)delete
DELETE FROM stu WHERE id=4;
4.3改update
修改表里的内容,一定要跟where字句一起使用
UPDATE stu SET sname='张三丰' WHERE id=1;
五:DQL数据检索语言(select)
5.1 单独使用,查看系统参数
查看系统参数
SELECT @@port;
SELECT @@datadir;
SELECT @@basedir;
SELECT @@server_id;
5.2 select 函数()
SELECT DATABASE(); ''''查看当前使用的哪个数据库''''
SELECT NOW(); ''''查看当前系统时间''''
SELECT USER(); ''''查看当前登录的用户是谁''''
concat字符串的拼接
select concat(user,'@','host') from mysql.user;
image.png
group_concat:将列转行,将几列数据转成一行显示
SELECT GROUP_CONCAT(USER,'@','host') FROM mysql.user;
image.png
5.3 select-from 子句
语法
select 列1,列2,列3 from 表名;
查询stu中所有的数据(不要对大表进行操作)
SELECT * FROM stu;
查询stu表中,学生姓名和入学时间
SELECT sname,intime FROM stu;
image.png
5.4 where子句
查询中国所有的城市信息
SELECT * FROM city WHERE COUNTRYCODE='CHN';
查询北京市的信息
SELECT * FROM city WHERE NAME='peking'
查询甘肃省所有城市的信息
SELECT * FROM city WHERE DISTRICT='GANSU'
查询世界上人口小于100w的城市
SELECT * FROM city WHERE population<1000000;
查询中国人口大于500w的城市
使用AND用法
SELECT * FROM city WHERE Countrycode='CHN' AND population>5000000
查询中国或者美国的城市信息
使用or用法
SELECT * FROM city WHERE countrycode='CHN' OR COUNTRYCODE='USA';
使用in()用法
SELECT * FROM city WHERE countrycode IN('CHN','USA')
查询世界上人口数量大于100w小于200w的城市信息
2种写法
SELECT * FROM city WHERE population>1000000 AND population<2000000
SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000
模糊查询
SELECT * FROM city WHERE district LIKE 'guang%'
'''''不要使用全模糊查询,百分号不能放在字符最前面,这样会影响数据库速度'''
5.5 group by+聚合函数
- 作用:将统计好的数据,按照by后面的条件进行分组排列
max() 最大值
min() 最小值
avg() 平均值
sum() 总和
count() 个数
group_councat() 列转行
统计世界上每个国家的总人口数sum()
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode;
统计中国各个省的总人口数量sum()
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
统计世界上每个国家的城市个数count()
SELECT countrycode,COUNT(NAME) FROM city GROUP BY countrycode
统计中国每个省的城市列表group_concat
SELECT district,GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district;
5.6 having 子句
- 作用:运行在group by后面,跟where的作用一样,只是优先级比where低
统计中国每个省的总人口数,只打印总人口数小于100w的
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)<1000000
5.7 order by +limit
- 作用:排序,默认从小到大,从大到小需要加desc
order by 条件 desc;
limit 显示数据中的某几列
limit 3:显示前3行
limit 3,7:跳过前3行之后,从第4行开始显示到第7行
查看中国所有的城市,并按人口数进行排序(从大到小)
SELECT NAME,population FROM city WHERE countrycode='CHN' ORDER BY population DESC;
统计中国各个省的总人口数量,按照总人口从大到小排序
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY SUM(population) DESC;
统计中国每个省的总人口,找到总人口大于500w的,并按总人口从大到小排序,只显示前三名
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population) DESC LIMIT 3;
5.8 distinct 去重复查询
SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city;
5.9 联合查询-union all
查询中国或者美国城市信息
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE COUNTRYCODE='USA'
6.0 子句的优先级顺序
select f1,f2......
from 表
where 过滤条件
group by 分组条件
having 过滤条件
order by 排序条件
limit 限制条件
