MySQL-SQL基础应用01
MySQL-SQL基础应用
1.sql 介绍
1. 什么是SQL？
关系型数据库当中通用的查询语言。全名：结构化查询语言。
2.sql 标准
SQL-89
SQL-92
SQL-99
SQL-03
5.7 以后符合SQL92严格模式
通过sql_mode参数来控制
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.sql 常用分类
DDL ： 数据定义语言
DCL ： 数据控制语言
DML ： 数据操作语言
4.数据类型、表属性、字符集
4.1 数据类型
4.1.1 作用
保证数据的准确性和标准性。
4.1.2 种类
#数值类型
tinyint ： -128~127
int ：-2^31~2^31-1
说明：手机号是无法存储到int的。一般是使用char类型来存储收集号
#字符类型
char(11) ：
定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
varchar(11):
变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。
enum('bj','tj','sh')：
枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用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。
timestamp会受到时区的影响
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
unique key ：唯一键
列值不能重复
unsigned ：无符号
针对数字列，非负数。
其他属性:
key :索引
可以在某列上建立索引，来优化查询
4.2 表属性
4.2.1列属性
约束(一般建表时添加):
**primary key** ：主键约束
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
**not null** ：非空约束
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
**unique key** ：唯一键
列值不能重复
**unsigned** ：无符号
针对数字列，非负数。
其他属性:
**key** :索引
可以在某列上建立索引，来优化查询,一般是根据需要后添加
**default** :默认值
列中，没有录入值时，会自动使用default的值填充
**auto_increment**:自增长
针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
**comment ** : 注释
4.2.2表属性
存储引擎:
InnoDB（默认的）
字符集和排序规则:
utf8
utf8mb4
4.3字符集和校对规则
4.3.1字符集
utf8
utf8mb4
4.3.2 校对规则
大小写是否敏感
5、DDL应用
5.1 数据定义语言
5.2 库定义语言
5.2.1创建
5.2.1 创建数据库
mysql> create database db charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> create database wordpress;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
建库规范：
1.库名不能有大写字母
2.建库要加字符集
3.库名不能有数字开头
4.库名要和业务相关
5.2.2 查询库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db |
| mysql |
| performance_schema |
| sys |
| test |
| wordpress |
+--------------------+
7 rows in set (0.00 sec)
mysql> show create database test;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create database wordpress;
+-----------+----------------------------------------------------------------------+
| Database | Create Database |
+-----------+----------------------------------------------------------------------+
| wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+-----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)
5.2.3 修改库
mysql> alter database wordpress charset utf8mb4;
Query OK, 1 row affected (0.01 sec)
5.2.4 删除库
mysql> drop database wordpress;
Query OK, 0 rows affected (0.01 sec)
#生产环境误用！！！！
5.3表定义
5.3.1 创建
create table stu(
列1 属性（数据类型、约束、其他属性） ，
列2 属性，
列3 属性
)
5.3.2 建表
mysql> CREATE TABLE stu (
-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
-> sname VARCHAR(64) NOT NULL COMMENT '姓名',
-> age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
-> gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
-> intime DATETIME NOT NULL COMMENT '入学时间'
-> )ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
Query OK, 0 rows affected (0.01 sec)
建表规范
1. 表名小写
2. 不能是数字开头
3. 注意字符集和存储引擎
4. 表名和业务有关
5. 选择合适的数据类型
6. 每个列都要有注释
7. 每个列设置为非空，无法保证非空，用0来填充。
5.3.3查表定义
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| 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(255) NOT NULL COMMENT '姓名',
`sage` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`sgender` enum('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
`sfz` char(18) NOT NULL COMMENT '身份证',
`intime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`id`),
UNIQUE KEY `sfz` (`sfz`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表' |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc stu;
+---------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(255) | NO | | NULL | |
| sage | tinyint(3) unsigned | NO | | 0 | |
| sgender | enum('m','f','n') | NO | | n | |
| sfz | char(18) | NO | UNI | NULL | |
| intime | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)
5.3.4 修改表的定义
-- 添加和删除字段
-- 1. 在表中添加telnum char(11) not null unique key comment '手机号'
ALTER TABLE stu ADD COLUMN telnum CHAR(11) NOT NULL UNIQUE KEY COMMENT '手机号';
-- 2. 在sname后添加a列
ALTER TABLE stu ADD COLUMN a INT NOT NULL COMMENT '测试列' AFTER sname;
-- 3. 在第一列前添加b列
ALTER TABLE stu ADD COLUMN b INT NOT NULL COMMENT '测试列' FIRST ;
-- 4. 删除添加的a,b列
ALTER TABLE stu DROP COLUMN a;
ALTER TABLE stu DROP COLUMN b;
-- 5. 修改数据类型
ALTER TABLE stu MODIFY telnum VARCHAR(20) NOT NULL UNIQUE KEY COMMENT '手机号';
ALTER TABLE stu MODIFY telnum VARCHAR(30) NOT NULL UNIQUE KEY COMMENT '手机号';
-- 6. 修改列名及数据类型
ALTER TABLE stu CHANGE telnum tel VARCHAR(64) NOT NULL UNIQUE KEY COMMENT '手机号';
5.3.5 删除表
mysql> show tables;
6.DCL应用
grant
revoke
7.DML应用
7.1 作用
对表中的数据行进行增、删、改
7.2 insert
--- 最标准的insert语句
INSERT INTO stu(id,sname,sage,sg,sfz,intime)
VALUES
(1,'zs',18,'m','123456',NOW());
SELECT * FROM stu;
--- 省事的写法
INSERT INTO stu
VALUES
(2,'ls',18,'m','1234567',NOW());
--- 针对性的录入数据
INSERT INTO stu(sname,sfz)
VALUES ('w5','34445788');
--- 同时录入多行数据
INSERT INTO stu(sname,sfz)
VALUES
('w55','3444578d8'),
('m6','1212313'),
('aa','123213123123');
SELECT * FROM stu;
_____________________________________________________________________________________________________
7.3 update
DESC stu;
SELECT * FROM stu;
UPDATE stu SET sname='zhao4' WHERE id=2;
注意：update语句必须要加where。
mysql> desc stu;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(64) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> select * from stu;
+----+------------+-----+--------+---------------------+
| id | sname | age | gender | intime |
+----+------------+-----+--------+---------------------+
| 1 | xiaolai | 18 | m | 2021-07-02 08:30:00 |
| 2 | laihecheng | 14 | f | 2021-07-02 08:30:00 |
| 3 | xiaowang | 34 | m | 2021-07-02 08:30:00 |
+----+------------+-----+--------+---------------------+
3 rows in set (0.00 sec)
mysql> update stu set sname='zhao4' where id=2;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stu;
+----+----------+-----+--------+---------------------+
| id | sname | age | gender | intime |
+----+----------+-----+--------+---------------------+
| 1 | xiaolai | 18 | m | 2021-07-02 08:30:00 |
| 2 | zhao4 | 14 | f | 2021-07-02 08:30:00 |
| 3 | xiaowang | 34 | m | 2021-07-02 08:30:00 |
+----+----------+-----+--------+---------------------+
3 rows in set (0.00 sec)
7.4 delete
mysql> select * from stu;
+----+----------+-----+--------+---------------------+
| id | sname | age | gender | intime |
+----+----------+-----+--------+---------------------+
| 1 | xiaolai | 18 | m | 2021-07-02 08:30:00 |
| 2 | zhao4 | 14 | f | 2021-07-02 08:30:00 |
| 3 | xiaowang | 34 | m | 2021-07-02 08:30:00 |
+----+----------+-----+--------+---------------------+
3 rows in set (0.00 sec)
DELETE FROM stu WHERE id=3;
mysql> select * from stu;
+----+---------+-----+--------+---------------------+
| id | sname | age | gender | intime |
+----+---------+-----+--------+---------------------+
| 1 | xiaolai | 18 | m | 2021-07-02 08:30:00 |
| 2 | zhao4 | 14 | f | 2021-07-02 08:30:00 |
+----+---------+-----+--------+---------------------+
2 rows in set (0.00 sec)
全表删除
DELETE FROM stu
truncate table stu;
区别:
delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.
truncate: DDL操作,对与表段中的数据页进行清空,速度快.
为删除：用update来替代delete，最终保证业务中查不到（select）即可
1.添加状态列
ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ;
SELECT * FROM stu;
2. UPDATE 替代 DELETE
UPDATE stu SET state=0 WHERE id=6;
3. 业务语句查询
SELECT * FROM stu WHERE state=1;
删除语句的对比
drop table t1 ; ---> 表定义+表数据（物理），全删除，磁盘空间立即删除
truncate table t1 ; ---> 清空表数据（物理），立即释放磁盘空间。
delete from t1; ---> 逐行删除表数据（逻辑，delete mark）。不会立即释放磁盘空间，会有碎片。
8.DQL应用（select）
8.1 单独使用
# 查询系统变量（参数）
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@server_id;
SELECT @@innodb_flush_log_at_trx_commit;
#替代方案：
SHOW VARIABLES;
SHOW VARIABLES LIKE '%trx%';
--select 函数（）
SELECT NOW();
SELECT DATABASE();
SELECT USER();
SELECT CONCAT("hello world");
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg
8.2单表字句-from
SELECT 列1,列2 FROM 表
SELECT * FROM 表
列子：
-- 查询stu中所有的数据(不要对大表进行操作)
SELECT * FROM stu ;
-- -- 查询stu表中,学生姓名和入学时间
SELECT sname , intime FROM stu;
sql语句练习
# 导入world练习库
-- https://dev.mysql.com/doc/index-other.html
-- [root@db01 ~]# mysql -uroot -p123 < world.sql
world ===>世界
city ===>城市
country ===>国家
countrylanguage ===>国家语言
city:城市表
DESC city;
ID : 城市ID
NAME : 城市名
CountryCode: 国家代码，比如中国CHN 美国USA
District : 区域
Population : 人口
SHOW CREATE TABLE city;
SELECT * FROM city WHERE id<10;
8.2.1 where 配合等值查询（select+from+where应用）
-- 查询中国(CHN)所有城市信息
SELECT * FROM city WHERE countrycode='CHN';
-- 查询北京市的信息
SELECT * FROM city WHERE NAME='peking';
-- 查询甘肃省所有城市信息
select * from city where district='gansu';
-- 查询美国(USA)所有的城市名和人口数
select name,Population from city where CountryCode='USA';
8.2.2 where 配合不等值查询 （> < >= <= !=）
-- 例子： 查询世界上人口数据小于100人的城市信息
select name,population from city where Population<100;
-- 例子：查询中国，并且人口大于500w的城市信息
select * from city where CountryCode='CHN' and Population>5000000;
-- 例子： 查询中国或美国的城市信息
select * from city where CountryCode='CHN' or CountryCode='USA';
#另一种写法
select * from city where countrycode in ('CHN','USA');
-- 例子： 查询人口数量在 100w-110w之间
select * from city where Population>=1000000 and Population<=1100000;
#另一种写法
select * from city where Population between 1000000 and 1100000;
8.2.3 where配合like应用
-- 查询countrycode是 “CH” 开头的城市信息
select * from city where countrycode like 'CH%';
8.2.4 select + from + where + group by +聚合函数应用
-- 聚合函数种类
count() : 统计个数
sum() : 求和
avg() ：平均值
max() ：最大值
min() ：最小值
group_concat()：列转行：
-- 统计city表，每个国家的城市个数
select CountryCode,count(*) from city group by countrycode;
-- 统计city表，中国 每个省的 城市个数
select district,count(*) from city where CountryCode='CHN' group by District;
-- 统计city表，每个国家的总人口数
select CountryCode,sum(Population) from city group by CountryCode;
-- 统计city表，中国 每个省的总人口数
select District,sum(Population) from city where CountryCode='CHN' group by District;
-- 统计city表，中国 每个省的 城市个数 ，所有城市名
select District,count(*),group_concat(name) from city where CountryCode='CHN' group by District;
select + from + where + group by + 聚合函数 + having 应用
-- 统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息
select District,sum(Population) from city where CountryCode='CHN' group by District having sum(population)>5000000;
select + from + where + group by + 聚合函数 + having +order by
-- 统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息,并按照总人口排序输出。
#有小到大排序
select district,sum(population) from city where countrycode='CHN' group by District having sum(Population)>5000000 order by sum(Population);
#有大到小排序
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population) desc;
select + from + where + group by + 聚合函数 + having +order by + limit
-- 统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息,并按照总人口从大倒小排序输出
-- 只显示前5名。
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 5 offset 0;
-- 只显示6-10名。
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 5 offset 5;
