mysql基础语句(下)
**插入字段<记录>insert：**
语法：`insert into 表名values (字段值1,字段值2, 字段值3);`
```
#插入记录时要对应相对的类型
mysql> insert into student values(1,'zhangs',21);
#同时插入多条，使用，分开
mysql> insert into student values(2,'lis',24),(3,'wange',26);
#指定字段插入
mysql> insert into students (id,name)values(4,'hangl');
```
查询表中记录：
语法：`select * from 表名称；`
```
mysql> select * from student; *表示所有
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | zhangs | 21 |
| 2 | lis | 24 |
| 3 | wange | 26 |
| 4 | hangl | NULL |
+------+--------+------+
#字段比较多的时候我们也可以使用\G
mysql> select * from student\G
#只查询表中某个字段的内容：
mysql> select name from student;
+--------+
| name |
+--------+
| zhangs |
| lis |
| wange |
| hangl |
+--------+
mysql> select id,name from student;
+------+--------+
| id | name |
+------+--------+
| 1 | zhangs |
| 2 | lis |
| 3 | wange |
| 4 | hangl |
+------+--------+
```
查看别的数据库的表或者不在本数据库上进行查看：
语法：`SELECT 字段 FROM 数据库名.表名;`
```
mysql> select *from HA.student; 查看某数据库下指定的表内容，数据库名.表名
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | zhangs | 21 |
| 2 | lis | 24 |
| 3 | wange | 26 |
| 4 | hangl | NULL |
+------+--------+------+
```
这样等效于先use 数据库，然后查看
**删除记录：**
```
#删除id为3的行
mysql> delete from students where id=3;
#删除age为空的行
mysql> delete from students where age is null;
#更新记录
mysql> update students set sex='M' where id=2;
#所有的都变为2
mysql> update students set id=2;
#同时更新多个字段时候用，号隔开
update students set stname='zhangsan',age=21 where uid=1;
```
SQL基础条件查询语句
语法：`select 字段名1，字段名2 from 表名 [where 条件];`
```
#1:查询students表中的name,age
mysql> select name,age from students;
+--------+------+
| name | age |
+--------+------+
| zhangs | 21 |
| lis | 24 |
| jk | 24 |
| lo | 26 |
| io | 25 |
| jk | 24 |
+--------+------+
#2：去重复查询distinct [dɪˈstɪŋkt]
mysql> select distinct name,age from students;
+--------+------+
| name | age |
+--------+------+
| zhangs | 21 |
| lis | 24 |
| jk | 24 |
| lo | 26 |
| io | 25 |
+--------+------+
mysql> select distinct id,name,age from students where id=3;
+------+------+------+
| id | name | age |
+------+------+------+
| 3 | jk | 24 |
+------+------+------+
#select distinct * from students; mysql的distinct可以对*使用
#3：使用and和or进行多条件查询
#or和and 同时存在时，先算and的两边值,逻辑与先执行
mysql> select id,name,age from students where id>3 and age>25;
+------+------+------+
| id | name | age |
+------+------+------+
| 5 | lo | 26 |
+------+------+------+
mysql> select id,name,age from students where id>3 or age>25;
+------+------+------+
| id | name | age |
+------+------+------+
| 5 | lo | 26 |
| 6 | io | 25 |
+------+------+------+
select * from students where stname='zhangsan' and (age=21 or age=24);
注意and和or都是用的时候的逻辑关系
```
**MySQL区分大小写查询：**
```
4：Mysql查询默认是不区分大小写的
mysql> select name from students where name='jk';
+------+
| name |
+------+
| jk |
| jk |
| JK |
+------+
解决
mysql> select * from students where binary name='jk';
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 3 | jk | 24 | W |
| 3 | jk | 24 | W |
+------+------+------+------+
BINARY是类型转换运算符，它用来强制它后面的字符串为一个二进制字符串，可以理解为在字符串比较的时候区分大小写。
```
5：MySQL查询排序：
语法：`select distinct 字段1，字段2 from 表名order by 字段名;`
```
默认为升序 asc
mysql> select distinct id from students order by id asc;
+------+
| id |
+------+
| 2 |
| 3 |
| 5 |
| 6 |
| 8 |
+------+
mysql> select distinct id from students order by id desc;
+------+
| id |
+------+
| 8 |
| 6 |
| 5 |
| 3 |
| 2 |
+------+
```
### 关于MySQL命令帮助
**help **
`mysql> help show;`
会告诉我们很多使用方法和信息
`mysql> help select;`
---
### 回顾前面的基础命令语句
修改数据表
创建表：
`create table 表名 (字段名 类型, 字段名 类型, 字段名 类型);`
查看创建表执行了哪些命令：
`show create table 表名 \G`
添加字段：
`alter table 表名 add 字段名 列类型 [not null|null][primary key][unique][auto_increment][default value]`
`alter table 表名 add 字段定义 after ar_id;`
删除字段：
`alter table 表名 drop 字段名;`
修改字段：
`alter table 表名 modify 字段名 字段新类型;`
完整修改字段：
`alter table 表名 change 旧字段名称 新字段定义;`
修改表名称
`alter table 表名 rename 新名字;`
删除表
`drop table [if (not) exists] 表名;`
# 表中行的操作
**insert**
`insert [into] 数据表名称 [(字段列表)] values|value (表达式|null|default,...),(表达式|null|default,...)`
`insert [into] 数据表名称 set 字段名称=值,...`
`insert与insert...set的区别是后者可以带有子查询。`
**update** -- 单表
`update 表名 set 字段名称=值,... [where 条件] `
如果省略WHERE条件将更新全部记录。
**delete** -- 单表
`delete from 数据表名称 [where 条件]`
如果省略where条件，将删除全部记录
**select**
`select 字段列表 from 数据表 [[as] 别名] [where 条件]`
别名的用法：
`Select * from 数据表 [[as] 别名]`
字段名称 [[as]别名]
`Select product_offer_instance_object_id as ID, product_offer_instance_object_name name,coumn33 ‘金额’From table
select btypeid as '图书类别ID',btypename as '图书类型' from category; `
select语句返回零条或多条记录；属于记录读操作
insert、update、delete只返回此次操作影响的记录数；属于写操作
## 数据库文件损坏最大可能性
1) kill -9 pid #强制
提示：使用kill -9 ID关闭数据库引起数据库故障案例。
`http://oldboy.blog.51cto.com/2561410/1431161`
2) 突然断电了.
3) 磁盘坏道.
# DML语句之管理表数据
建立oldboy库,stu表
```
mysql> create database oldboy;
Query OK, 1 row affected (0.02 sec)
mysql> use oldboy;
Database changed
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 '性别', telnum
-> CHAR(15) NOT NULL DEFAULT '0' COMMENT '手机号' )ENGINE=INNODB CHARSET=utf8mb4
-> COMMENT '学生表';
Query OK, 0 rows affected (0.05 sec)
```
## 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');
```
特殊:
insert into stu(sname) values('oldgirl');
备份数据:
```
[root@db01 ~]# mysqldump -uroot -poldboy123 oldboy >/opt/bak.sql
mysqldump: [Warning] Using a password on the command line interface can beinsecure.
[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.修改表的数据
练习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 -poldboy123 oldboy</opt/bak.sql`
3.生产经验：禁止不带where条件操作数据库表
方法1：临时执行mysql> set global sql_safe_updates=1;，退出重新登陆生效。
方法2：把sql_safe_updates=1加入到my.cnf的mysqld标签下，重启数据库。
方法3：设置别名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
sql_safe_updates作用:
在update操作中：当where条件中列没有索引可用,且无limit限制时会拒绝更新。
## 3.删除表的数据
```
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
```
不让我用delete，但是我又有删除需求如何解决?
## 4.伪删除数据
### 1.添加state状态字段，默认为1。
`ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 COMMENT `
'状态:1为存在,0为不存在';
### 2.显示数据语句:
```
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 |
| 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 |
+----+---------+-----+--------+--------+-------+
```
## 5.生产经验:
等号后面字符串和数字,如何引用?
1)字符串必须用引号引起来。
2)数字不要引起来。
注意：不要只看内容显示的表象，而要看实际列类型。
## 6.清空表的数据
`truncate table stu`
## 7.面试题：
`drop table stu`、`truncate table stu`、`delete from stu`，三个删除语句的区别 ?
答：三者相同点是都会删除表中的数据
区别说明
`drop table stu;`：
同时删除表本身和表中数据，会立即释放磁盘空间，速度比TRUNCATE慢。
`truncate table stu;`：
删除表中所有数据，表本身未删除， 释放数据页空间，并且只在事务日志中记录页的释放。并立即释放磁盘空间,速度快。
`delete from stu;`：
逐行"删除"，逻辑删除表中的每行数据，表本身未删除,并在事务日志中为所删除的每行记录一项,不会立即释放磁盘空间。
# 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
```
mysql>source ~/day02_world_oldboy.sql;
mysql> use world
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
```
3）查看表结构
```
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int | NO | PRI | NULL |auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
```
4）采用WHERE+等值(=)查询
```
#查询中国的所有城市信息，*表示所有列，CHN为中国代号。
SELECT * FROM city WHERE countrycode='CHN';
#只查询中国的所有城市的名称和人口数量列，CHN为中国代号。
SELECT NAME,population FROM city WHERE countrycode='CHN';
```
5）采用WHERE + 条件判断(</>/>=/<=/!=)查询
```
#查询大于700万人的所有城市信息
SELECT * FROM city WHERE population>7000000;
#查询小于等于1000人的所有城市信息
SELECT * FROM city WHERE population<=1000;
```
6）采用WHERE +逻辑判断符(AND/OR ) 查询
```
#查询中国境内，大于520万人口的城市信息
SELECT * FROM city WHERE countrycode='CHN' AND population>5200000;#<==同时满足两个
条件。
#查询中国和美国的所有城市
SELECT * FROM city WHERE countrycode='chn' OR countrycode='USA';
```
7）采用WHERE + LIKE模糊查询(搜索框搜索)
```
#查询国家代号是CH开头的城市信息
SELECT * FROM city WHERE countrycode LIKE 'CH%';
SELECT * FROM city WHERE countrycode LIKE '%US%';
```
8）采用WHERE + BETWEEN+AND区间查询
```
#查询人口数在100w到200w之间的城市信息，以下两条命令等同。
SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;
SELECT * FROM city WHERE population>=1000000 AND population<=2000000;
```
# 用SQL命令查看MySQL数据库大小
1、进入information_schema 数据库（存放了其他的数据库的信息）
`use information_schema;`
2、查询所有数据的大小：
`select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;`
3、查看指定数据库的大小：
比如查看数据库home的大小
`select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';`
4、查看指定数据库的某个表的大小
比如查看数据库home中 members 表的大小
`select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='members';`