09-SQL语句基础与体系结构讲解
1.SQL介绍
1.什么是SQL
SQL，英文全称为Structured Query Language，中文是结构化查询语言，是一种对关系数据库数据定义和操作语言。
2.SQL分类
DDL DDL全拼Data Definition Language，数据定义语言， [运维、DBA常用]
CREATE（创建）、ALTER（修改）、DROP（删除）
? Data Definition
特点:增删库\表\索引本身.
DCL DCL全拼Data Control Language，数据控制语言，[运维、DBA常用]
主要关键字为GRANT（用户授权）、REVOKE（权限回收）、COMMIT（提交）、ROLLBACK（回滚）。
执行mysql> ? Account Management，可查相关帮助。
DML DML全拼Data Manipulation Language，数据操作语言，[开发人员常用,运维用的少,DBA审核开发SQL语句].
主要关键字为INSERT（增）、DELETE（删）、UPDATE（改），主要针对数据库里的表里的数据进行操作。
执行mysql> ? Data Manipulation，可查相关帮助
DQL DQL全称Data Query Language，数据查询语言，作用是从表中获取数据。
关键字SELECT是用得最多的，相关常用保留字有WHERE、ORDER BY、GROUP BY和HAVING等。
2.SQL语句实践
0.DDL语句show查询
# 显示所有数据库的列表。
show databases;
# 显示当前数据库中所有表的列表。
show tables;
# 查看oldboy库里有多少个表
show tables from oldboy;
# 查字符集列表及校对规则
show charset;
# 查权限列表
show privileges;
# 查用户授权
show grants for oldboy@'10.0.0.%';
# 查建库语句
show create database oldboy;
# 查建表语句
show create table oldboy.stu;
# 查正在运行sql语句
show processlist;
show full processlist;
#查mysql配置参数:可以存在于my.cnf中。
show variables;
show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------+
| log_bin | ON |
| log_bin_basename | /data/3306/data/binlog |
| log_bin_index | /data/3306/data/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------+
6 rows in set (0.00 sec)
==========================================
# 查看数据库运行状态:ZABBIX监控数据库数据从这里获取。
show status;
# 查看全局状态
show global status;
# 查看引擎innodb的状态
show engine innodb status;
解读：
https://blog.csdn.net/github_26672553/article/details/52931263
# 查看索引
show index from mysql.user;
# 查看引擎
show engines;
show查询主从复制相关
show binary logs; #二进制日志，记录更改数据库信息的语句 create insert update drop alter
SHOW BINLOG EVENTS IN 'binlog.000013'; # 查看某一个事件
SHOW RELAYLOG EVENTS # 查看中继日志事件
show master status; # 查看主的状态
show slave status; # 查看从的状态
show slave hosts; # 查看同步的主机
show plugins; # 查看插件
1.DDL语句只管理库
1.创建数据库
# 语法：
# 默认建库,字符集是utf8mb4
CREATE DATABASE dbanme;
# 指定字符集和校对规则建库
CREATE DATABASE db_name CHARACTER SET charset_name COLLATE collation_name
# 例子：
CREATE DATABASE oldboy;
# 创建一个名为 "oldgirl" 的数据库，并将其默认字符集设置为 UTF-8，排序规则设置为不区分大小写和重音符号。
# UTF-8 是一种常用的字符编码，支持包括中文在内的各种字符。
# 排序规则决定了数据库在执行比较和排序操作时使用的规则
# "utf8_general_ci" 是一种不区分大小写、不区分重音符号的排序规则，常用于多语言环境。
CREATE DATABASE oldgirl default character SET utf8 COLLATE utf8_general_ci;
# 默认创建相当于指定如下字符集
CREATE DATABASE oldboy; # 与下面是等价的(8.0.26)
# utf8mb4 是一种用于存储 Unicode 字符的字符集，支持存储任意的 Unicode 字符，包括表情符号等。
# "utf8mb4_0900_ai_ci" 是一种不区分大小写、不区分重音符号的排序规则，适用于 Unicode 字符集。
CREATE DATABASE oldgirl DEFAULT character SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
# 从0创建任意字符集的数据库
# 问：字符集和校对规则去哪查？
show charset;
? CREATE DATABASE
=========================
CREATE DATABASE db_name
CHARACTER SET gbk
COLLATE gbk_chinese_ci;
2.查看建库语句
查看默认创建库的语句结果
mysql> show create database oldboy;
+----------+--------------------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------------------+
| oldboy | CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create database oldgirl\G # 竖向格式化显示
*************************** 1. row ***************************
Database: oldgirl
Create Database: CREATE DATABASE `oldgirl` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)
3.字符集和校对规则
show charset; #查支持的字符集和校对规则
show collation; #查支持的校对规则
4.有关字符集说明
8.0默认字符集是utf8mb4，是当前主流字符集，适合英文\中文,PC,移动端,物联网多场景。
utf8mb4大于utf8(utf8mb3)，类似ext4大于ext3
重点知识：通过命令查出的建库语句结尾多了一些东西，其中DEFAULT CHARACTER SET utf8mb4，是数据库默认的字符集8.0之后是utf8mb4，8.0之前是latin1，查询字符集的命令为：show charset;。COLLATE utf8mb4_0900_ai_ci为校对规则，查询校对规则的命令为：show collation; 字符集的不一致会导致数据库中文内容出现乱码，后文章节会详细讲解字符集和校对规则，此处完全默认即可。
5.查看数据库
查看数据库的常用命令（可help show获得帮助）是：
show databases; #<==查询所有数据库。
show databases like 'oldboy'; #<==匹配oldboy字符串的内容。
show databases like 'oldboy%'; #<==%为通配符，表示匹配以oldboy开头的所有内容。
6.切换数据库
mysql> use oldboy # cd /oldboy 进入到指定的库。
mysql> select database(); # 查看当前用户所在库pwd。
+------------+
| database() |
+------------+
| oldboy |
+------------+
1 row in set (0.00 sec)
mysql> select user(); # 查看当前用户，类似whoami。
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
7.修改数据库
#语法:
ALTER DATABASE [db_name]
CHARACTER SET = charset_name
COLLATE = collation_name;
#例子：修改字符集为utf8
alter database oldboy charset utf8;
8.删除数据库
drop database oldboy;
9.管理库生产经验
1.管理员谨慎使用drop命令。
2.业务用户中，禁止有drop权限。
3.数据库名不能用大写字母,不能是关键字，不能使数字开头。
4.创建数据库时应显示设置字符集。
CREATE DATABASE oldboy CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
表知识小总结:
1.数据类型
2.约束属性
3.引擎\字符集
4.SELECT,insert,update,delete
5.管理索引(表的列上)
2.DDL语句之管理表
1.表数据类型
表的数据类型作用于[列上]的，用于控制存储数据的格式和规范。
a.整型
表1：
| 列类型 | 存储容量 | 正数数字范围 | 负数数字范围 | 说明 |
|---|---|---|---|---|
| tinyint（微小整型） | 1bytes | 0-255 | -128~127 | 最大3位数 |
| int | 4bytes | 0-2^32-1 | -231~231-1 | 最大10位数 |
| bigint | 8bytes | 0-2^64-1 | -263~263-1 | 最大20位数 |
UNSIGNED：它表示该列只能存储非负整数值（包括零）
b.浮点型（小数）
除了整数之外，还有浮点数，一般用于对于精度要求很高的业务，例如和钱有关的业务，例如：99.99。 可以使用放大倍数手法把浮点数变成整数存储，例如：99.99放大100倍，为9999，使用在缩小100倍。 提示：在数据库里以整数形式存储,提高读取效率.
面试题：
1.请描述tinyint、int、 bigint区别？
2.你们公司怎么存储浮点数数据的?
解答：金钱(精度要求高)有关的decimal，精度要求不高的，统一放大10的n次方倍，变成整型存储。
c.字符类型
char(10) 定长，浪费存储空间，作为条件列查询会更快。
varchar(10) 变长，节省空间
面试题：char(10) 和 varchar(10) 区别?
1.相同点：都是字符串类型，最多都只能存10个字符。
2.不同点
char 定长 浪费存储空间,作为条件列查询会更快(不要计算,直接读取内容相对多)
varchar 变长 节省空间,没有极特殊情况,尽量都用varchar
存储说明:
char(10) 存储 ' abc',左边补7个空格。
varchar(10) 存储 'abc'。
d.时间类型
时间类型往往是数据表中的必备类型
| 列类型 | 存储容量 | 说明 |
|---|---|---|
| timestamp | 4字节 | 从1970-1-1到2038-1-19，秒数 |
| datetime | 8字节 | 从1000-1-1到9999-12-31 |
问题1:字符串类型可以放数字么?
可以，还是字符串类型。
问题2:那要数字类型还有啥用?
有用.
1.数字可以用来计算的，而字符串不行.
2.数字查询效率更高.
e.枚举类型
判断，选择：enum('N','Y')
性别：enum('F','M','N')
省份：固定多个简单值的内容。
mysql> show create table mysql.user\G
`account_locked` enum('N','Y')
`Create_role_priv` enum('N','Y')
`Drop_role_priv` enum('N','Y')
f. 二进制类型
文件\图片
g. json
ELK、mongodb存储格式
2.表的约束属性
| 类型 | 说明 |
|---|---|
| PRIMARY KEY | 简写PK，设置于表主键列，非空且唯一，用于必填项且不能重复。 |
| NOT NULL /NULL | 表示列的内容是否非空，空列不利于数据库优化。 |
| UNIQUE KEY | 简写UK，表示列的内容唯一，#比如手机号，就不能重复。 |
| FOREIGN KEY | 简写FK，表示表的外键,多个表之间关联用的。 |
生产经验：
1.每张表务必设置一个主键列，建议是数字且自增，学号，身份证，订单编号。
2.表的每个列尽量设置非空，给默认值。
3.表的其他属性
| 类型 | 说明 |
|---|---|
| AUTO_INCREMENT [=] value | 设置主键自增长，一般为数字自增长，默认为1。 |
| COMMENT [=] 'string' | 对列等信息进行注释。 |
| ENGINE [=] engine_name | 指定表的底层存储引擎(MySQL文件系统)，默认为 innodb。 |
| CHARACTER SET [=] charset_name | 指定表的字符集，默认为utf8mb4。 |
| COLLATE [=] collation_name | 指定表的校对规则 |
| DEFAULT | 设定列的默认值，，如果是时间列取当前时间。 |
| unsigned | 无符号数字(非负数) |
4.建表
1)建库
drop database oldboy;
create database oldboy;
use oldboy;
2）在oldboy库创建表stu
create table <表名> (
<字段名1> <类型1> ...,
<字段名2> <类型1> ...,
…
<字段名n> <类型n>
);
下面是创建stu表的完整语句示例，表名为stu。
USE oldboy;
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 '性别',
telnum CHAR(15) NOT NULL DEFAULT '0' COMMENT '手机号'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
释义
id：整数类型，非空，主键，自增，用于表示学号。
sname：字符串类型，最大长度为64，非空，用于表示姓名。
age：无符号小整数类型，非空，默认为0，用于表示年龄。
gender：枚举类型，包含'M'、'F'和'N'三个值，非空，默认为'N'，用于表示性别。
telnum：字符类型，固定长度为15，非空，默认为'0'，用于表示手机号码。
该表使用InnoDB引擎，字符集为utf8mb4，表的注释为"学生表"。
5.查看表信息
# 查看表信息
show tables;或show tables from oldboy;
# 查看建表语句
show create table stu\G
# 查看表结构
mysql> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int NOT NULL 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 '性别',
`telnum` char(15) NOT NULL DEFAULT '0' COMMENT '手机号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表'
1 row in set (0.02 sec)
# 查看表字段
mysql> desc stu;
+--------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| sname | varchar(64) | NO | | NULL | |
| age | tinyint unsigned | NO | | 0 | |
| gender | enum('M','F','N') | NO | | N | |
| telnum | char(15) | NO | | 0 | |
+--------+-------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
6.更改表名
rename table stu to test;
alter table test rename to stu;
7.增删改表字段(开发人员常用,了解)
（1）添加列默认到所有列结尾，命令语法为：
alter table 表名 {add|drop|modify} 字段 类型 其他;
#1.在stu表中添加addr列，默认是最后一列
ALTER TABLE stu ADD addr VARCHAR(100) NOT NULL COMMENT '地址';
#2.在sname列后插入dept列。使用after sname;
alter table stu add dept varchar(32) after sname;
#3.在首列插入qq列。使用first
alter table stu add qq varchar(15) first;
#4.若要同时添加以上两个字段，还可采用如下命令
alter table stu drop dept;
alter table stu drop qq;
alter table stu add dept varchar(32) after sname,add qq varchar(15) first;
#5.修改字段类型的命令如下：
alter table stu modify dept char(64); #<==将dept数据类型及长度改为char(64)。
#6.删除列的命令如下
alter table stu drop qq;
alter table stu drop dept;
alter table stu drop addr;
有关字段修改生产经验:
1.如何改字段
需要开发把增删改字段语句,发给运维或者DBA，由DBA审核后,由DBA在线上执行。
2.online DDL,最好不做.添加到最后一个字段可以,其他情况在业务低谷执行,紧急情况pt工具.
常把数字列用于主键,只有数字主键才能自增.主键列不一定用数字.
8.删除表
drop table stu;
9.有关DDL表生产规范和经验
请简述你们公司在Schema设计过程中有哪些开发规范？
-- 库的DDL规范
1. 禁止线上业务系统出现DROP操作.
2. 库名: 不能大写字母,不能是关键字,不能使数字开头.一般和业务有关.
3. 显式设置字符集.
-- 表的DDL规范
1. 建表时,表名小写, 建议格式: wp_user,不要出现数字开头和大写字母
2. 显式的设置存储引擎\字符集\表的注释.
3. 列名要和业务有关
4. 列的数据类型,讲究:完整\简短\合适,精度不高浮点数,放大N倍.
5. 每个表必须要有主键,数字自增无关列.
6. 每个列尽量是非空的,而且设置默认值.
7. 每个列要有注释.
8. 变长列,一般选择varchar类型,定长列一般选择char.
9. 大字段,可以选择附件形式,可以选择ES.
10. 对于Online-DDL ,对于追加方式添加列,可以online,添加索引可以online(8.0)
其他情况下,需要在数据库低谷时间点去做.如果很紧急,pt-osc或者gh-ost
3. DML语句之管理表数据
建立oldboy库,stu表
create database oldboy;
use oldboy;
drop table stu;
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 '性别', telnum CHAR(15) NOT NULL DEFAULT '0' COMMENT '手机号' )ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
释义
id：整数类型，非空，主键，自增，用于表示学号。
sname：字符串类型，最大长度为64，非空，用于表示姓名。
age：无符号小整数类型，非空，默认为0，用于表示年龄。
gender：枚举类型，包含'M'、'F'和'N'三个值，非空，默认为'N'，用于表示性别。
telnum：字符类型，固定长度为15，非空，默认为'0'，用于表示手机号码。
该表使用InnoDB引擎，字符集为utf8mb4，表的注释为"学生表"。
1.往表中插入数据
INSERT关键字
第一种：指定列插入
INSERT INTO stu(id,sname,age,gender,telnum) VALUES(1,'oldboy',28,'M','111');
# 注意：数字不加引号,字符加引号
# 不要看长相,看字段属性,确定是否是字符和数字.
第二种：按顺序插入，可以不指定列（重点）
INSERT INTO stu VALUES(2,'oldgril',25,'F','126');
第三种：批量插入多行(节省IO)，注意缩进。
INSERT INTO
stu
VALUES
(3,'Jack',18,'M','189'),
(4,'Tim',35,'F','183');
模拟批量插入:
delete from stu;
select * from stu;
INSERT INTO `stu`
VALUES
(1,'oldboy',28,'M','111'),
(2,'oldgril',25,'F','126'),
(3,'Jack',18,'M','189'),
(4,'Tim',35,'F','183');
只插入部分列,需要按顺序指定列名,且其他列允许为空,不允许为空有默认值
mysql> INSERT INTO stu(id,sname) VALUES(2,'oldgril');
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu;
+----+---------+------+-----+--------+--------+
| id | sname | dept | age | gender | telnum |
+----+---------+------+-----+--------+--------+
| 2 | oldgril | NULL | 0 | N | 0 |
+----+---------+------+-----+--------+--------+
备份数据:
[root@db01 ~]# mysqldump -uroot -p123 -B oldboy >/opt/bak.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@db01 ~]# egrep -v "\-\-|\*|^$" /opt/bak.sql
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
`id` int NOT NULL 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 '性别',
`telnum` char(15) NOT NULL DEFAULT '0' COMMENT '手机号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表';
LOCK TABLES `stu` WRITE;
INSERT INTO `stu` VALUES (1,'oldboy',28,'M','111'),(2,'oldgril',25,'F','126'),(3,'Jack',18,'M','189'),(4,'Tim',35,'F','183');
UNLOCK TABLES;
2.修改表的数据
update关键字,语法:
update 表名 set 字段='要改成的内容' where 条件列='内容';
练习1:把名字为Tim的人的年龄改为29
update stu set age=29 where sname='Tim';
练习2:把id为2的行的名字改为girl
update stu set sname='girl' where id=2;
练习3:把手机号为189的行,性别改为N?
update stu set gender='N' where telnum='189';
练习4.修改数据导致的事故案例和解决方案
不加条件执行下面语句:
update stu set sname='oldboy';
恢复：mysql -uroot -p123 oldboy</opt/bak.sql
3.生产经验：
1.禁止不带where条件操作数据库表
2.带where条件,也必须对查询的列建立索引
禁止不带where条件操作数据库表4种方法:
服务端[mysqld]两种方法:
sql_safe_updates作用:
在delete,update操作中,
1)没有where条件.
2)当where条件中列没有索引可用.
3)且无limit限制时会拒绝更新。
方法1：临时执行mysql> set global sql_safe_updates=1;，退出重新登陆生效。自己控制自己.
方法2：永久生效
sql_safe_updates=1参数在my.cnf中添加启动报错。通过启动加载脚本方式来修改。
[root@db01 ~]# vi /etc/my.cnf
[mysqld]
init_file=/opt/init.sql
#新建脚本
echo 'set global sql_safe_updates=1;' >/opt/init.sql
chmod +x /opt/init.sql
/etc/init.d/mysqld restart
[root@db01 ~]# mysql -uroot -p123 -e "select @@global.sql_safe_updates"
+---------------------------+
| @@global.sql_safe_updates |
+---------------------------+
| 1 |
+---------------------------+
客户端两种方法:mysql客户端防止误删
方法1：把safe_updates=1加入到my.cnf的client标签下。
[root@db01 ~]# cat /etc/my.cnf
#by oldboy weixin:oldboy0102
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
port=3306
socket=/tmp/mysql.sock
[client]
socket=/tmp/mysql.sock
safe_updates=1
注意:把sql_safe_updates=1加入到my.cnf的mysqld标签下这个不好用.
使用永久生效模式
方法2：设置别名alias mysql='mysql -U' ，并放入/etc/profile永久生效。
设置后效果：
mysql> update oldboy.stu set sname='oldboy';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE
4.删除表的数据
delete from 表名 where 列名 条件;
mysql> use oldboy
mysql> delete from stu where id=3; #<==删除id为3的行。
mysql> delete from stu where age=28; #<==删除name 等于oldboy的行。
生产经验:禁止使用delete语句，数据库删除不用drop,delete,truncate
因为有安全更新参数所以age没有索引,不让删除.
mysql> delete from stu where age>28;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
创建索引:
mysql> alter table stu add index i_age(age);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+--------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| sname | varchar(64) | NO | | NULL | |
| age | tinyint unsigned | NO | MUL | 0 | |
| gender | enum('M','F','N') | NO | | N | |
| telnum | char(15) | NO | | 0 | |
+--------+-------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
#可以删除了
mysql> delete from stu where age>28;
Query OK, 1 row affected (0.00 sec)
*不让我用delete，但是我又有删除需求如何解决?*
5.伪删除数据(了解)
#1.添加state状态字段，默认为1。
ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1为存在,0为不存在';
mysql> desc stu;
+--------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| sname | varchar(64) | NO | | NULL | |
| age | tinyint unsigned | NO | | 0 | |
| gender | enum('M','F','N') | NO | | N | |
| telnum | char(15) | NO | | 0 | |
| state | tinyint | NO | | 1 | | ###增加一列
+--------+-------------------+------+-----+---------+----------------+
2.显示数据语句:用下面语句
mysql> select * from stu where state=1; ###select * from stu
+----+---------+-----+--------+--------+-------+
| id | sname | age | gender | telnum | state |
+----+---------+-----+--------+--------+-------+
| 1 | oldboy | 28 | M | 111 | 1 |
| 2 | oldgril | 25 | F | 126 | 1 |
| 3 | Jack | 18 | M | 189 | 1 |
| 4 | Tim | 35 | F | 183 | 1 |
+----+---------+-----+--------+--------+-------+
3.伪删除
mysql> update stu set state=0 where id=4; #<==delete from stu where id=4;
# 删除结果:
mysql> select * from stu where state=1;
+----+---------+-----+--------+--------+-------+
| id | sname | age | gender | telnum | state |
+----+---------+-----+--------+--------+-------+
| 1 | oldboy | 28 | M | 111 | 1 |
| 2 | oldgril | 25 | F | 126 | 1 |
| 3 | Jack | 18 | M | 189 | 1 |
+----+---------+-----+--------+--------+-------+
# 实际上没有删除:
mysql> select * from stu;
+----+---------+-----+--------+--------+-------+
| id | sname | age | gender | telnum | state |
+----+---------+-----+--------+--------+-------+
| 1 | oldboy | 28 | M | 111 | 1 |
| 2 | oldgril | 25 | F | 126 | 1 |
| 3 | Jack | 18 | M | 189 | 1 |
| 4 | Tim | 35 | F | 183 | 0 |
+----+---------+-----+--------+--------+-------+
6.生产经验:
等号后面字符串和数字,如何引用?
1)字符串必须用引号引起来。
2)数字不要引起来。
注意：不要只看内容显示的表象，而要看实际列类型。
7.清空表的数据
truncate table stu
8.企业面试题：
drop table stu、truncate table stu、delete from stu，三个删除语句的区别 ?
答：三者相同点是都会删除表中的数据
区别说明
**drop table stu：**
同时删除表本身和表中数据，
会立即释放磁盘空间，速度比TRUNCATE慢。
**truncate table stu**：
删除表中所有数据，表本身未删除，
释放[数据页page]空间，并且只在事务日志中记录页的释放。并立即释放磁盘空间,速度快。
**delete from stu**：
逐行"删除"，逻辑删除表中的每行数据，表本身未删除
并在事务日志中为所删除的每行记录一项,不会立即释放磁盘空间。
4.DQL语句之查询表中的数据
关键字select
1.查询表配置参数及函数
（1）查询系统变量以及配置参数
mysql> select @@datadir; #<==查看数据目录，也可用show variables like '%datadir%';
mysql> select @@socket; #<==显示socket路径。
（2）执行函数
mysql> select now(); #<==显示当前日期时间。
mysql> select database(); #<==显示当前所在的库。
mysql> select user(); #<==显示当前的用户。
(3) 计算
mysql> select 100*(100+1)/2;
+---------------+
| 100*(100+1)/2 |
+---------------+
| 5050.0000 |
+---------------+
mysql> select 100*(100+1)/2+(0.1+0.5);
+-------------------------+
| 100*(100+1)/2+(0.1+0.5) |
+-------------------------+
| 5050.6000 |
+-------------------------+
2.查询表中的数据(重点)
1）[基础]命令语法为：
select <字段1，字段2，...> from <表名> [WHERE 条件]
2）测试数据:day02_world_oldboy.sql提前上传到Linux
上传文件到linux的根目录
然后到mysql里进行查看
# 这是通过system查看系统文件
mysql> system ls
day02_world_oldboy.sql mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz mysqld3307 mysqld3308
mysql> system pwd
/root
# 把文件内容传输进去
mysql>source ~/day02_world_oldboy.sql;
mysql> use world
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city | # 城市
| country | # 国家
| countrylanguage | # 国家语言
+-----------------+
3 rows in set (0.00 sec)
3）查看表结构
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int | NO | PRI | NULL | auto_increment | # id
| Name | char(35) | NO | | | | # 名称
| CountryCode | char(3) | NO | MUL| | | # 国家代号
| District | char(20) | NO | | | | # 地域
| Population | int | NO | | 0 | | # 人口
+-------------+----------+------+-----+---------+----------------+
4）采用WHERE+等值(=)查询
#查询中国的所有城市信息
分析:
*表示所有列，CHN为中国代号。
mysql> select * from city where countrycode='CHN';
+------+---------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+----------------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1893 | Tianjin | CHN | Tianjin | 5286800 |
| 1894 | Wuhan | CHN | Hubei | 4344600 |
#只查询中国的所有城市的名称和人口数量，CHN为中国代号。
mysql> select name,population from city where countrycode='CHN';
+---------------------+------------+
| name | population |
+---------------------+------------+
| Shanghai | 9696300 |
| Peking | 7472000 |
| Chongqing | 6351600 |
| Tianjin | 5286800 |
| Wuhan | 4344600 |
5）采用WHERE + 条件判断(</>/>=/<=/!=)查询
#查询大于700万人的所有城市信息
mysql> select * from city where population>7000000;
+------+-------------------+-------------+------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-------------------+-------------+------------------+------------+
| 206 | São Paulo | BRA | São Paulo | 9968485 |
| 456 | London | GBR | England | 7285000 |
| 939 | Jakarta | IDN | Jakarta Raya | 9604900 |
| 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 |
| 1025 | Delhi | IND | Delhi | 7206704 |
| 1532 | Tokyo | JPN | Tokyo-to | 7980230 |
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 2331 | Seoul | KOR | Seoul | 9981619 |
| 2515 | Ciudad de México | MEX | Distrito Federal | 8591309 |
| 2822 | Karachi | PAK | Sindh | 9269265 |
| 3357 | Istanbul | TUR | Istanbul | 8787958 |
| 3580 | Moscow | RUS | Moscow (City) | 8389200 |
| 3793 | New York | USA | New York | 8008278 |
+------+-------------------+-------------+------------------+------------+
14 rows in set (0.00 sec)
#查询小于等于1000人的所有城市信息
mysql> select * from city where population<=1000;
+------+---------------------+-------------+-------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+-------------+------------+
| 61 | South Hill | AIA | – | 961 |
| 62 | The Valley | AIA | – | 595 |
| 1791 | Flying Fish Cove | CXR | – | 700 |
| 2316 | Bantam | CCK | Home Island | 503 |
| 2317 | West Island | CCK | West Island | 167 |
| 2728 | Yaren | NRU | – | 559 |
| 2805 | Alofi | NIU | – | 682 |
| 2806 | Kingston | NFK | – | 800 |
| 2912 | Adamstown | PCN | – | 42 |
| 3333 | Fakaofo | TKL | Fakaofo | 300 |
| 3538 | Città del Vaticano | VAT | – | 455 |
+------+---------------------+-------------+-------------+------------+
成功要素:自信心,责任心,恒心
6）采用WHERE +逻辑判断符(AND/OR ) 查询
#查询中国境内，大于520万人口的城市信息
mysql> select * from city where countrycode='CHN' and population>5200000; #<==同时满足两个条件。
+------+-----------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------+-------------+-----------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1893 | Tianjin | CHN | Tianjin | 5286800 |
+------+-----------+-------------+-----------+------------+
4 rows in set (0.00 sec)
#查询中国和美国的所有城市
mysql> select * from city where countrycode='chn' or countrycode='usa';
+------+-------------------------+-------------+----------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-------------------------+-------------+----------------------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1893 | Tianjin | CHN | Tianjin | 5286800 |
| 3841 | Saint Louis | USA | Missouri | 348189 |
| 3842 | Wichita | USA | Kansas | 344284 |
| 3843 | Santa Ana | USA | California | 337977 |
7）采用WHERE + LIKE模糊查询(早期用于网站搜索框搜索)
#查询国家代号是CH开头的城市信息
SELECT * FROM city WHERE countrycode LIKE 'CH%'; ##CHXXXX
SELECT * FROM city WHERE countrycode LIKE '%US%'; ##ASDFASDFASDUSFASDF
查询慢的解决:
1.早期用于网站搜索框搜索,现在改为用ES做搜索.
2.Redis缓存
3.去一个从库查
8）采用WHERE + BETWEEN+AND区间查询
#查询人口数在100w到200w之间的城市信息，以下两条命令等同。
SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;
SELECT * FROM city WHERE population>=1000000 AND population<=2000000;
作业:
见单独文件
来了解下 DataGrip，我们为解决专业 SQL 开发者的特定需求量身定做的全新数据库 IDE。
数据库客户端datagrip，写sql语句有自动提示，还能检测错误，并提示解决办法
浙公网安备 33010602011771号