操作
数据库基本操作
1.数据库设置密码
[root@db02 scripts]# mysqladmin -uroot password 123
2.使用密码登录
#1.正确的方式（不规范.
[root@db02 scripts]# mysql -uroot -p123
[root@db02 scripts]# mysql -u root -p123
#2.错误的方式
[root@db02 scripts]# mysql -uroot -p 123
#官网上说-p或者--password参数与密码之间不能有间隔或多于字符
#3.正确且规范的方式
[root@db02 scripts]# mysql -u root -p
Enter password:
3.查询用户
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | db02 |
| root | db02 |
| | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)
4.删除用户（优化.
mysql> drop user root@'::1';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| | db02 |
| root | db02 |
| | localhost |
| root | localhost |
+------+-----------+
5 rows in set (0.00 sec)
企业误删除用户故障
1.不小心删除了mysql所有用户
mysql> delete from mysql.user where 1=1;
Query OK, 5 rows affected (0.00 sec)
#删除用户以后还是可以登陆，但是不要重启
解决方式一：
1.停止数据库
#抽根烟冷静一下
[root@db02 scripts]# systemctl stop mysqld
2.跳过授权表和网络启动
[root@db02 scripts]# mysqld_safe --skip-grant-tables --skip-networking &
3.插入新的用户
insert into mysql.user values ('localhost','root',PASSWORD('123'),
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'',
'',
'',
'',0,0,0,0,'mysql_native_password','','N');
4.重启启动数据库
[root@db02 scripts]# mysqladmin shutdown
200709 16:27:19 mysqld_safe mysqld from pid file /usr/local/mysql/data/db02.pid ended
[1]+ Done mysqld_safe --skip-grant-tables --skip-networking
[root@db02 scripts]# systemctl start mysqld
解决方式二：
1.停止数据库
#抽根烟冷静一下
[root@db02 scripts]# systemctl stop mysqld
2.跳过授权表和网络启动
[root@db02 scripts]# mysqld_safe --skip-grant-tables --skip-networking &
3.授权一个新用户
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to root@'localhost' identified by '123' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql体系结构管理
客户端与服务端模型
# 数据是一个典型的C/S结构的服务
1.mysql自带的客户端工具
mysql
mysqladmin
mysqldump
2.mysql是一个二进制程序，后台守护进程
单进程
多线程
# mysql的连接方式
1.TCP/IP连接
2.socket连接
#tcp/ip连接
mysql -uroot -p -h127.0.0.1
mysql -uroot -p -h127.0.0.1 -S /tmp/mysql.sock
#socket连接
mysql -uroot -p -hlocalhost
mysql -uroot -p
#注意：
1.不一定-h就是TCP/IP连接，如果是localhosts就是socket连接
2.socket连接比TCP/IP连接快，TCP/IP连接需要经过三次握手，四次挥手，慢
3.socket连接只能本地连接，远程不能使用
MySQL服务构成
# 什么是实例
1.MySQL的后台进程 + 线程 + 预分配的内存空间
2.MySQL在启动的过程中会启动后台守护进程，并生成工作线程，预分配内存结构供MySQL处理数据使用。
MySQL服务程序构成
# 连接层
1.验证用户身份，判断用户名和密码
2.提供两种连接方式
3.与SQL层建立交互的线程
# sql层
1.接收连接层传过来的数据
2.判断sql语句的语法
3.判断SQL语句的语义
4.解析器：解析sql语句，生成执行计划
5.优化器：选出执行计划中最优的计划
6.执行器：执行计划
6.1 与存储引擎层建立交互
6.2 将优化后要执行的SQL发给存储引擎层
7.记录缓存，如果前端有redis，将数据缓存至redis
8.记录日志
# 存储引擎层
1.与sql层建立交互，接收SQL层传来的SQL语句
2.与磁盘进行交互，获取数据，将数据返回给sql层
MySQL逻辑结构
1.库：库的下面全是表
2.表：元数据 + 真实数据行
3.元数据：列 + 其它属性（行数+占用空间大小+权限）
4.列：列名字 + 数据类型 + 其他约束（非空、唯一、主键、非负数、自增长、默认值）
MySQL物理结构
1.MySQL的最底层的物理结构是数据文件，也就是说，存储引擎层，打交道的文件，是数据文件。
2.存储引擎分为很多种类（Linux中的FS）
3.不同存储引擎的区别：存储方式、安全性、性能
#数据库创建时，不论库名还是表名，尽量都用小写字母
MySQL物理大小
1.段：理论上一个表就是一个段，由多个区构成，（分区表是一个分区一个段）
2.区：连续的多个页构成
3.页：最小的数据存储单元，默认是16k
4.分区表：一个区构成一张段，就是一张表
SQL语句
SQL语句的语义种类
- DDL: 数据定义语言 Data Definition Language
- DCL: 数据控制语言 Data Control Language
- DML: 数据操作语言 Data Manipulate Language
- DQL: 数据查询语言 Data Query Language
DDL: 数据定义语言 （create、drop）
create针对库的操作
1）语法
mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
2）创建库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> create SCHEMA db2;
Query OK, 1 row affected (0.00 sec)
3）建库时提示已存在
mysql> create database db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
mysql> create database IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> create database IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
4）查看建库语句
mysql> show create database db1;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
5）创建数据库并指定字符集
mysql> create database db3 charset utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db3;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
#不指定校验规则默认就是 utf8_general_ci
mysql> create database db4 charset utf8;
Query OK, 1 row affected (0.00 sec)
6）删库
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
7）修改库
mysql> show create database db2;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database db2 charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db2;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
create针对表的操作
1）语法
mysql> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
2）建表
#1.进入一个库
mysql> use db2
Database changed
#2.查看当前所在库
mysql> select database();
+------------+
| database() |
+------------+
| db2 |
+------------+
1 row in set (0.00 sec)
{1}
#3.建表，建表最少有一列
mysql> create table tb1;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)
{1}
#4.查看表
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| tb1 |
+---------------+
1 row in set (0.00 sec)
{1}
mysql> desc tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
{1}
3）数据类型
int 整数 -2^31 - 2^31-1 (-2147483648 - 2147483647)
tinyint 最小整数 -128 - 127 #年龄 0 - 255
varchar 字符类型（变长） #身份证
char 字符类型（定长）
enum 枚举类型 #给它固定选项，只能选则选择项中的值 性别
datetime 时间类型 年月日时分秒
{1}
4）数据类型测试
#int类型
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)
{1}
mysql> insert tb1 values(1);
Query OK, 1 row affected (0.01 sec)
{1}
mysql> insert tb1 values(11111111111);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert tb1 values(2147483647);
Query OK, 1 row affected (0.00 sec)
{1}
mysql> insert tb1 values(2147483648);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert tb1 values(-2147483648);
Query OK, 1 row affected (0.00 sec)
{1}
mysql> insert tb1 values(-2147483649);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
#enum类型
mysql> create table qiudao(id int,sex enum('nan','nv'));
Query OK, 0 rows affected (0.02 sec)
{1}
mysql> insert into qiudao values(1,'nan');
Query OK, 1 row affected (0.00 sec)
{1}
mysql> insert into qiudao values(1,'qiudao');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
5）建表测试
表名：student
id
name
age
gender
cometime
#1.建表
mysql> create table student({1}
-> id int,
-> name varchar(12),
-> age tinyint,
-> gender enum('M','F'),
-> cometime datetime);
Query OK, 0 rows affected (0.01 sec)
{1}
#2.插入数据
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.00 sec)
{1}
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.01 sec)
{1}
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.00 sec)
{1}
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.01 sec)
{1}
#3.查看数据
mysql> select * from student;
+------+--------+------+--------+---------------------+
| id | name | age | gender | cometime |
+------+--------+------+--------+---------------------+
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:04 |
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:08 |
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:09 |
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:10 |
+------+--------+------+--------+---------------------+
4 rows in set (0.00 sec)
{1}
6）建表数据属性
not null： #非空
primary key： #主键（唯一且非空的）
auto_increment： #自增（此列必须是：primary key或者unique key）
unique key： #唯一键，单独的唯一的
default： #默认值
unsigned： #非负数
comment： #注释
{1}
7）加上属性建表
#1.建表语句
create table students(
id int primary key auto_increment comment "学生id",
name varchar(12) not null comment "学生姓名",
age tinyint unsigned not null comment "学生年龄",
gender enum('M','F') default 'M' comment "学生性别",
cometime datetime default now() comment "入学时间");
#2.查看建表语句
mysql> show create table students;
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`name` varchar(12) NOT NULL COMMENT '学生姓名',
`age` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
`gender` enum('M','F') DEFAULT 'M' COMMENT '学生性别',
`cometime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
{1}
#3.插入数据
mysql> insert into students values(1,'qiudao',18,'M',now());
Query OK, 1 row affected (0.00 sec)
#因为主键相同无法插入
mysql> insert into students values(1,'qiudao',18,'M',now());
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
#应该
mysql> insert into students values('2','qiudao',18,'M',now());
Query OK, 1 row affected (0.00 sec)
#主键已经设置自增没必要自己插入
{1}
#正规插入数据的写法
mysql> insert students(name,age) values('lhd',18);
Query OK, 1 row affected (0.00 sec)
{1}
mysql> insert students(name,age) values('lhd',12);
Query OK, 1 row affected (0.01 sec)
{1}
mysql> select * from students;
+----+--------+-----+--------+---------------------+
| id | name | age | gender | cometime |
+----+--------+-----+--------+---------------------+
| 1 | qiudao | 18 | M | 2020-07-14 19:51:44 |
| 2 | qiudao | 18 | M | 2020-07-14 19:52:19 |
| 3 | lhd | 18 | M | 2020-07-14 19:53:50 |
| 4 | lhd | 12 | M | 2020-07-14 19:53:58 |
+----+--------+-----+--------+---------------------+
4 rows in set (0.00 sec)
{1}
## 删除表
drop table student;
修改表
#1.新建表
mysql> create table linux(daijiadong tinyint);
Query OK, 0 rows affected (0.04 sec
#2.修改表名
mysql> alter table linux rename linux9;
Query OK, 0 rows affected (0.01 sec)
#3.插入新字段
mysql> alter table linux9 add rengyufeng int;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| daijiadong | tinyint(4) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
{1}
#4.插入多个新字段
mysql> alter table linux9 add liukong int,add wangzhangxing int;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
{1}
mysql> desc linux9;
+---------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| daijiadong | tinyint(4) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#5.插入字段到最前面
mysql> alter table linux9 add kangpeiwen varchar(100) first;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| daijiadong | tinyint(4) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
#6.插入字段到指定字段后面
mysql> alter table linux9 add chenjianqing varchar(100) after daijiadong;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| daijiadong | tinyint(4) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
{1}
#7.删除指定列
mysql> alter table linux9 drop daijiadong;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
{1}
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
{1}
#8.修改字段
mysql> alter table linux9 change rengyufeng congtianqi int;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
{1}
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| congtianqi | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
{1}
#9.修改字段属性
mysql> alter table linux9 modify congtianqi tinyint;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
{1}
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| congtianqi | tinyint(4) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
DCL: 数据控制语言 （grant、revoke）
grant授权
#1.授权语句
grant all on *.* to root@'172.16.1.%' identified by '123';
#2.查看用户权限
mysql> show grants for root@'localhost';
| Grants for root@localhost |
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
#3.扩展参数
max_queries_per_hour：一个用户每小时可发出的查询数量
mysql> grant all on *.* to root@'172.16.1.%' identified by '123' with max_queries_per_hour 2;
Query OK, 0 rows affected (0.00 sec)
max_updates_per_hour：一个用户每小时可发出的更新数量
mysql> grant all on *.* to root@'172.16.1.%' identified by '123' with max_updates_per_hour 2;
Query OK, 0 rows affected (0.00 sec)
max_connetions_per_hour：一个用户每小时可连接到服务器的次数
mysql> grant all on *.* to lhd@'172.16.1.%' identified by '123' with max_connections_per_hour 2;
Query OK, 0 rows affected (0.00 sec)
max_user_connetions：允许同时连接数量
mysql> grant all on *.* to lhd@'172.16.1.%' identified by '123' with max_user_connections 1;
Query OK, 0 rows affected (0.00 sec)
revoke回收权限
mysql> revoke drop on *.* from lhd@'172.16.1.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for lhd@'172.16.1.%';
| Grants for lhd@172.16.1.%
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'lhd'@'172.16.1.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH MAX_CONNECTIONS_PER_HOUR 2 MAX_USER_CONNECTIONS 1
#所有权限
SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DROP, GRANT
授权超级管理员
grant all on *.* to root@'172.16.1.%' identified by '123' with grant option;
DML数据操作语言（insert、delete、update）
insert 命令
1）查看表结构
mysql> desc student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(12) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| gender | enum('m','f') | YES | | f | |
| cometime | datetime | YES | | CURRENT_TIMESTAMP | |
| birthday | datetime | YES | | NULL | |
+----------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)
{1}
2）插入数据（不规范写法）
mysql> insert into student values(1,'邱导',78,'f',now(),'1942-07-14');
Query OK, 1 row affected (0.00 sec)
{1}
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 78 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
1 row in set (0.00 sec)
{1}
3）插入数据（规范写法）
#1.插入指定列数据
mysql> insert into student(name,age) values('曾导','84');
Query OK, 1 row affected (0.00 sec)
{1}
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 78 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 78 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
+----+--------+-----+--------+---------------------+---------------------+
3 rows in set (0.00 sec)
{1}
#2.插入指定列数据
mysql> insert into student(name,age,birthday) values('曾导','84','1936-02-20');
Query OK, 1 row affected (0.00 sec)
{1}
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 78 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 78 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
4 rows in set (0.00 sec)
4）插入多条数据
mysql> insert into student(name,age,birthday) values('好大','18',1936-02-21),('好小','28','1992-01-01');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 78 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 78 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 |
| 8 | 好小 | 28 | f | 2020-07-15 09:31:07 | 1992-01-01 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
8 rows in set (0.00 sec)
update命令
1）查看数据
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 78 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 78 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 |
| 8 | 好小 | 28 | f | 2020-07-15 09:31:07 | 1992-01-01 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
8 rows in set (0.00 sec)
{1}
2）修改数据
#使用update语句必须要加where条件
mysql> update student set age=18 where name='邱导';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
{1}
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 18 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 18 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 |
| 8 | 好小 | 28 | f | 2020-07-15 09:31:07 | 1992-01-01 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
8 rows in set (0.00 sec)
3）指点修改一条数据
#如果数据库有主键，一定使用主键
mysql> update student set age=88 where name='邱导' and cometime='2020-07-15 09:21:12';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update student set age=88 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 88 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 88 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 |
| 8 | 好小 | 28 | f | 2020-07-15 09:31:07 | 1992-01-01 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
8 rows in set (0.00 sec)
delete语句
1）删除数据
#1.先查看数据，确认要删除的数据，怎么确定唯一
#2.使用delete语句也一定要加where条件
mysql> delete from student where id=8;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+
| id | name | age | gender | cometime | birthday |
+----+--------+-----+--------+---------------------+---------------------+
| 1 | 邱导 | 88 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 |
| 2 | 邱导 | 88 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 |
+----+--------+-----+--------+---------------------+---------------------+
7 rows in set (0.00 sec)
#3.如果就是要清空表
mysql> delete from student where 1=1;
Query OK, 1 row affected (0.01 sec)
#(危险)
truncate table student;
drop table student
4.使用update代替delete
1）添加状态字段
mysql> alter table student add status enum('1','0') default 1;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+--------+
| id | name | age | gender | cometime | birthday | status |
+----+--------+-----+--------+---------------------+---------------------+--------+
| 1 | 邱导 | 88 | f | 2020-07-15 09:21:12 | 1942-07-14 00:00:00 | 1 |
| 2 | 邱导 | 88 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 | 1 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL | 1 |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 | 1 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 | 1 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 | 1 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 | 1 |
+----+--------+-----+--------+---------------------+---------------------+--------+
7 rows in set (0.00 sec)
2）使用update代替delete
#相当于删除学生
mysql> update student set status='0' where id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+--------+
| id | name | age | gender | cometime | birthday | status |
+----+--------+-----+--------+---------------------+---------------------+--------+
| 2 | 邱导 | 88 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 | 0 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL | 1 |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 | 1 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 | 1 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 | 1 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 | 1 |
+----+--------+-----+--------+---------------------+---------------------+--------+
6 rows in set (0.00 sec)
#相当于学生回来
mysql> update student set status='1' where id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+--------+-----+--------+---------------------+---------------------+--------+
| id | name | age | gender | cometime | birthday | status |
+----+--------+-----+--------+---------------------+---------------------+--------+
| 2 | 邱导 | 88 | f | 2020-07-15 09:22:27 | 1942-07-14 00:00:00 | 1 |
| 3 | 曾导 | 84 | f | 2020-07-15 09:24:17 | NULL | 1 |
| 4 | 曾导 | 84 | f | 2020-07-15 09:25:22 | 1936-02-20 00:00:00 | 1 |
| 5 | 曾导 | 84 | f | 2020-07-15 09:28:11 | 1936-02-21 00:00:00 | 1 |
| 6 | 好大 | 18 | f | 2020-07-15 09:29:18 | 0000-00-00 00:00:00 | 1 |
| 7 | 好大 | 18 | f | 2020-07-15 09:31:07 | 0000-00-00 00:00:00 | 1 |
+----+--------+-----+--------+---------------------+---------------------+--------+
6 rows in set (0.00 sec)
DQL数据查询语言（select、desc）
select查询语句
1）查询表中所有的数据
#很危险，数据量过大，容易导致down机
mysql> select * from student;
#先查询数据总量，然后决定是否可以查询所有数据
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.01 sec)
2）查看指定列的数据
mysql> select user,host from mysql.user;
+--------+------------+
| user | host |
+--------+------------+
| root | % |
| root | 127.0.0.1 |
| lhd | 172.16.1.% |
| qiudao | 172.16.1.% |
| root | 172.16.1.% |
| root | ::1 |
| | db03 |
| root | db03 |
| | localhost |
| root | localhost |
+--------+------------+
10 rows in set (0.01 sec)
{1}
3）按条件查询
mysql> select name,gender from student where name='邱导';
+--------+--------+
| name | gender |
+--------+--------+
| 邱导 | f |
+--------+--------+
1 row in set (0.00 sec)
简单查询练习
1）将sql文件导入数据
#方式一：
[root@db03 ~]# mysql -uroot -p123 < world.sql
#方式二:
mysql> source /root/world.sql;
#方式三:
mysql> \. /root/world.sql;
2）查看数据
mysql> use world;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.00 sec)
mysql> select * from city;
3）查询练习
#1.查看表结构
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
#2.查看所有数据
mysql> select * from city;
#3.查看指定列的数据
mysql> select Name,Population from city;
#4.查看数据时排序（按照人口数量）
#升序
mysql> select Name,Population from city order by Population;
#降序
mysql> select Name,Population from city order by Population desc;
#5.查询部分数据
#查看前十条数据
mysql> select Name,Population from city order by Population desc limit 10;
#6.按照步长查询数据
mysql> select id,Name,Population from city limit 50,50;
#50起始位置 50步长
条件查询
#1.条件查询就是使用where语句，where语句可以使用的符号
条件符号：= < > <= >= != <> or and like
精确匹配：=
范围匹配：< > <= >= != <>
模糊匹配：like
连接语句：or and
#2.查询中国的城市人口
mysql> select name,population from city where CountryCode='CHN';
#3.查询黑龙江人口数量
mysql> select name,population from city where countrycode='CHN' and District='heilongjiang';
#4.查询中国人口数量小于100000的城市
mysql> select name,population from city where countrycode='CHN' and population < 100000;
#5.模糊匹配
#匹配以N结尾的数据
mysql> select name,countrycode from city where countrycode like '%N';
#匹配以N开头的数据
mysql> select name,countrycode from city where countrycode like 'N%';
#匹配包含N的数据
mysql> select name,countrycode from city where countrycode like '%N%';
#6.查询中国或美国的人口数量
#使用or
mysql> select name,population from city where countrycode = 'CHN' or countrycode = 'USA';
#使用in
mysql> select name,population from city where countrycode in ('CHN','USA');
#使用union all
mysql> select name,population from city where countrycode = 'CHN' union all select name,population from city where countrycode = 'USA';
select高级用法多表联查，联表查询
多表联查，联表查询
传统连接
1）集合
#集合
[qiudao,zengdao,qiandao]
[80,90,100]
#数据库
id:[1,2,3]
name:[qiudao,zengdao,qiandao]
id:[1,2,3]
mark:[80,90,100]
2）建表
mysql> create table students(id int,name varchar(10));
Query OK, 0 rows affected (0.08 sec)
mysql> create table score(id int,mark int);
Query OK, 0 rows affected (0.05 sec)
3）插入数据
mysql> insert into students values(1,'qiudao'),(2,'qiandao'),(3,'zengdao');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into score values(1,80),(2,90),(3,100);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
4）数据查询
#查看两个表的数据
mysql> select * from students;
+------+---------+
| id | name |
+------+---------+
| 1 | qiudao |
| 2 | qiandao |
| 3 | zengdao |
+------+---------+
3 rows in set (0.00 sec)
mysql> select * from score;
+------+------+
| id | mark |
+------+------+
| 1 | 80 |
| 2 | 90 |
| 3 | 100 |
+------+------+
3 rows in set (0.00 sec)
#查看邱导的分数
mysql> select students.name,score.mark from students,score where students.id=1 and score.id=1;
mysql> select students.name,score.mark from students,score where students.id=score.id and name='qiudao';
+--------+------+
| name | mark |
+--------+------+
| qiudao | 80 |
+--------+------+
1 row in set (0.01 sec)
#查询所有学生成绩
mysql> select students.name,score.mark from students,score where students.id=score.id
5）练习题一：
**连表查询：世界上小于100人的城市在哪个国家？请列出城市名字，国家名字与人口数量**
#1.确认我要查哪些内容
国家名字 城市名字 城市人口数量 小于100人
#2.确认在哪个表
country.name city.name city.population
#3.找出两个表相关联的字段
city.countrycode country.code
#4.编写语句
mysql> select country.name,city.name,city.population from country,city where city.countrycode=country.code and city.population < 100;
+----------+-----------+------------+
| name | name | population |
+----------+-----------+------------+
| Pitcairn | Adamstown | 42 |
+----------+-----------+------------+
1 row in set (0.01 sec)
6）练习题二：
**连表查询：世界上小于100人的城市在哪个国家，是用什么语言？请列出城市名字，国家名字与人口数量和国家语言**
#1.确认我要查哪些内容
国家名字 城市名字 城市人口数量 国家使用的语言 小于100人
#2.确认在哪个表
country.name city.name city.population countrylanguage.language
#3.找出三个表相关联的字段
country.code city.countrycode countrylanguage.countrycode
#4.写sql语句
mysql> select country.name,city.name,city.population,countrylanguage.language from country,city,countrylanguage where country.code=city.countrycode and city.countrycode=countrylanguage.countrycode and city.population < 100;
+----------+-----------+------------+-------------+
| name | name | population | language |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown | 42 | Pitcairnese |
+----------+-----------+------------+-------------+
1 row in set (0.04 sec)
#1.分析要哪些内容？
城市的人口数量 城市名字 国家名字 国家语言
#2.分析数据所在库
city.population city.name country.name countrylanguage.language
#3.找出三个表相关联内容
city.countrycode country.code countrylanguage.countrycode
#3.编写语句
select country.name,city.name,city.population,countrylanguage.language from city,country,countrylanguage where city.countrycode=country.code and country.code=countrylanguage.countrycode and city.population < 100;
自连接
#自己查找相同字段，使用自连接，两个关联的表必须有相同字段和相同数据
SELECT city.name,city.countrycode,countrylanguage.language,city.population
FROM city NATURAL JOIN countrylanguage
WHERE population > 1000000
ORDER BY population;
#两个表中没有相同字段不行，字段相同值不同不行
SELECT country.name,city.name,city.population FROM city NATURAL JOIN country WHERE population < 100;
#注意：
1.自连接必须有相同字段和相同值
2.两个表中的数据必须完全相同
内连接
1）语法格式
select * from 表1 join 表2 on 相关联的条件 where 条件;
#注意：命中率（驱动的概念）
表1 小表
表2 大表
select * from 表1 inner join 表2 on 相关联的条件 where 条件;
2）例子1：两表联查
#小于100人的城市在哪个国家，国家代码是什么？
select city.name,city.population,city.countrycode,country.name
from city join country on city.countrycode=country.code
where city.population < 100;
3）例子2：三表联查
#世界上小于100人的城市在哪个国家？是用什么语言？
select country.name,city.name,city.population,countrylanguage.language
from city join country on city.countrycode=country.code
join countrylanguage on country.code=countrylanguage.countrycode
where city.population < 100;
外连接（有问题）
1）左外连接
select city.name,city.countrycode,country.name,city.population
from city left join country
on city.countrycode=country.code
and city.population < 100;
2）右外连接
select city.name,city.countrycode,country.name,city.population
from city right join country
on city.countrycode=country.code
and city.population < 100;