MySQL必学4大语言

MySQL必学4种语言

本篇文章记录的是MySQL中常用的4种语言：

DDL

DDL，data defination language，指的是数据定义语言，其主要作用是创建数据库，对库表的结构进行删除和修改等操作。

进入数据库

1 2	mysql -uroot -p -- 使用这种方式，接下来需要输入密码。密码是暗文 mysql -uroot -p123456 -- 可以直接将密码123456放在参数p的后面，不安全

参数解释：

u：指定用户
p：指定密码

全部命令

1. 数据库操作
show databases;     // 显示所有的数据库
use school;     // 使用school数据库
create database school;  // 创建数据库
drop database school;  // 删除某个数据库

2. 表操作
-- 创建表
create table user(字段1,字段2,...,字段n);

-- 查看创建表的SQL语句
show create table user;

-- 查看表的结构
desc user;

-- 删除表
drop table user;

-- 修改表名
alter table user rename to users;

数据库操作

show databases;     // 显示所有的数据库
use school;     // 使用school数据库
create database school;  // 创建数据库
drop database school;  // 删除某个数据库
mysql> show databases;   // 显示数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| peter              |
| school             |
| sys                |
+--------------------+
6 rows in set (0.04 sec)

mysql> use school;   // 选择使用数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

表操作

// 1、创建表
create table user(字段1,
                  字段2,
                  ...,
                  字段n
                  );    // 最后的分号不能忘记

// 2、查看所有的表
show tables;

// 3、查看表的结构
desc user;

// 4、查看创建表的SQL语句
show create table user;

// 5、删除表
drop table user;

// 6、修改表名
alter table user rename to users;   # 表名改为users；to可省略

最后的分号不能忘记😢

mysql> use school;  // 使用一个数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;  //  查看数据库下的所有表
+------------------+
| Tables_in_school |
+------------------+
| course           |
| score            |
| student          |
| teacher          |
| total            |
+------------------+
5 rows in set (0.00 sec)

创建表

主键primary key 和auto_increment必须连在一起使用
书写规范：每个字段的语句最好分行写，容易检查
最后的分号不能忘记

# 创建user表：6种字段+1个主键

create table user( id int(10) unsigned not null auto_increment comment "user_id",   //将id作为主键
                  name varchar(20) not null comment "user_name",
                  email varchar(50) not null comment "user_email",
                  age tinyint unsigned not null comment "user_age",
                  fee decimal(10,2) not null default 0.00 comment "user_fee",
                  createTime timestamp not null comment "user_time",
                  primary key(id)
                 );    // 记得分号

查看表结构

mysql> desc user;
+------------+---------------------+------+-----+-------------------+-----------------------------+
| Field      | Type                | Null | Key | Default           | Extra                       |
+------------+---------------------+------+-----+-------------------+-----------------------------+
| id         | int(10) unsigned    | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(20)         | NO   |     | NULL              |                             |
| email      | varchar(50)         | NO   |     | NULL              |                             |
| age        | tinyint(3) unsigned | NO   |     | NULL              |                             |
| fee        | decimal(10,2)       | NO   |     | 0.00              |                             |
| createTime | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)

查看创建表的`SQL`语句

1	show create table user;

字段操作

关键词是alter，先选中需要操作的表。

modify：修改
change：改变名字
add：添加字段
- 默认是末尾
- 指定位置添加

// 修改字段信息
alter table user modify name varchar(50) not null;   # 将字段name 从20改为50个字符

// 修改字段名字
alter table user change email user_email varchar(50) not null;  # 将email改成user_email

// 末尾添加字段
alter table user add password char(30) not null comment "user_password";   # 增加password字段

// 指定位置添加字段
alter table user add password1 char(30) not null comment "user_password1" after user_name;   # 在name后面增加password1字段

// 删除字段
alter table user drop password1;        #删除字段password1
// 原来的表格信息

mysql> desc user;
+------------+---------------------+------+-----+-------------------+-----------------------------+
| Field      | Type                | Null | Key | Default           | Extra                       |
+------------+---------------------+------+-----+-------------------+-----------------------------+
| id         | int(10) unsigned    | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(20)         | NO   |     | NULL              |                             |
| email      | varchar(50)         | NO   |     | NULL              |                             |
| age        | tinyint(3) unsigned | NO   |     | NULL              |                             |
| fee        | decimal(10,2)       | NO   |     | 0.00              |                             |
| createTime | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

# 修改字段信息
mysql> alter table user modify name varchar(50);
# 修改字段名字
mysql> alter table user change email user_email varchar(50) not null;
# 添加字段，末尾
mysql> alter table user add password char(30) not null comment "user_password";
# 指定位置添加字段
mysql> alter table user add password1 char(30) not null comment "user_password1" after name;

mysql> desc user;
+------------+---------------------+------+-----+-------------------+-----------------------------+
| Field      | Type                | Null | Key | Default           | Extra                       |
+------------+---------------------+------+-----+-------------------+-----------------------------+
| id         | int(10) unsigned    | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(50)         | YES  |     | NULL              |                             |
| password1  | char(30)            | NO   |     | NULL              |                             |
| user_email | varchar(50)         | NO   |     | NULL              |                             |
| age        | tinyint(3) unsigned | NO   |     | NULL              |                             |
| fee        | decimal(10,2)       | NO   |     | 0.00              |                             |
| createTime | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| password   | char(30)            | NO   |     | NULL              |                             |
+------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

DML

DML，data manipulation language，指的是数据操作语言。主要是对数据库中的表记录进行操作的语言，包含往表中插入数据、表中数据的更新、表的删除等

表中插入数据
表中数据更新
删除表

表中插入数据

-- 将字段名和字段的值一一对应起来，可以只插入部分字段
-- 省略了id和createtime字段
mysql> insert into user(
            name,
            email,
            age,
            fee,
            password)
values("xiaoming",
           "123456@qq.com",
           20,
           56.56,
           Password("xiaoming")     // 密码这里要用函数Password()
      );
-- 包含所有字段信息
insert into user values(10, "nanan", "78173828@qq.com", 38, 89.19, 2019-10-02, Password("nanan"));

字符串字段必须用引号括起来
密码需要使用函数Password()
语句末尾加分号
利用只插入部分字段
可以省去字段名，此时需要加上id，而且必须填写所有的字段信息，不能只添加部分数据

数据更新

数据更新update使用最多的是where语句，指定某个条件下执行；如果不加where，则所有的字段都会被更改（慎重）

指定id号
指定字段的具体值
字段允许有多个，用逗号隔开

mysql> update user set name="nangying" where id=6;   // 通过id指定
mysql> update user set fee=88.76 where fee=56.90;   // 通过字段名直接指定
mysql> update user set email="81847919@qq.com", age=54 where id=7;  // 同时修改多个值
mysql> update user set fee=88.88 where id in(2,4,6);   // in的用法
mysql> update user set fee=66.66 where id between 2 and 6;  // between ... and ...

删除

删除表有两种情况：

delete：删除表，插入数据从上一次结束的id号开始继续插入；删除的记录仍存在
truncate：清空表，重新插入数据id从1开始；不占内存空间

1 2	delete table user; truncate table user;

删除delete表中的某条记录

1 2	delete from user where id=7; // 删除记录 insert into user (name,email,age,fee,password) values("lisi","9837194@qq.com", 36, 81.17, Password("lisi")); // id是从原来的基础上递增

关于truncate

# 删除数据
mysql> truncate table user;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from user;
Empty set (0.00 sec)

# 重新插入数据
mysql> insert into user (user_name, user_email, user_age, password, fee) values ("peter", "123456a@163.com", 27, password("101010"), 28.87);
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> select * from user;
+----+-----------+-----------------+----------+--------------------------------+-------+
| id | user_name | user_email      | user_age | password                       | fee   |
+----+-----------+-----------------+----------+--------------------------------+-------+
|  1 | peter     | 123456a@163.com |       27 | *C3BC3E91915DCAE22014892F9827D | 28.87 |
+----+-----------+-----------------+----------+--------------------------------+-------+
1 row in set (0.00 sec)

DCL

data control language，DCL，指的是数据控制语言，主要是对数据库中的登录和用户的权限进行控制的语言，包含

用户登录MySQL数据库
修改用户密码及忘记密码如何解决
创建普通用户及授权
撤销权限revoke
查看权限及删除用户

全部命令

1. 查看数据库中的用户及信息
mysql -uroot -p
show databases;
use mysql;
show tables;
select user, host, passord from user;    # 所有的用户都在user表中

2. 创建新用户、授权、撤销权限和删除
-- 创建
create user "test"@"192.168.2.10" identified by"password";    # 指定用户test、ip和密码password
flush privileges;  # 刷新权限
mysql -utest -h192.168.2.10 -p  # 用test用户登录

-- 授权
grant select, insert, delete on shop.* to "test"@"192.168.2.10";   # shop 是数据库，test是数据库中的表
flush privileges;  # 刷新权限
systemctl restart mysql;  # 重启mysql

-- 创建用户的同时进行授权
grant select, insert, delete on shop.* to "test"@"192.168.2.10" identified  by"password";

-- 查看权限
show grants for "test"@"192.168.2.10"\G   # \G参数是为了输出好看

-- 撤销权限
revoke delete on shop.* to "test"@"192.168.2.10";   # 撤销shop数据库中test用户的delete权限
flush privileges;  # 刷新权限
systemctl restart mysql;  # 重启mysql

-- 删除用户
drop user "test"@"192.168.2.10";

-- 谨慎操作
grant all privileges on *.* to "test"@"192.168.2.10"  # 将所有的权限给所有数据库


3. 修改用户密码
-- 已知用户原密码，能够进行登录
mysql -uroot -p
show databases;
use mysql;
show tables;
select user, host, password from user;    # 所有的用户都在user表中
update user set password=PASSWORD("123456admin") where user="test";  # 将test用户的密码改成123456admin
flush privileges;

-- 忘记原来的密码：借助跳跃权限表，重启守护进程
mysql skip-grant-tables  # 跳跃权限表
mysql  # 重新进入mysql
show databases;use mysql;show tables;
select user, host, password from user;    # 所有的用户都在user表中
update user set password=PASSWORD("123456admin") where user="test";  # 将test用户的密码改成123456admin
flush privileges;

4. 查看mysql服务
-- window
直接去任务管理器
-- linux
netstat -an   # 找到3306端口

关于`root`账户

默认情况下，MySQL数据库是指允许root账户登录并且在本机上登录的。

-uroot表示root账户
-p表示需要密码
没有-h表示默认是本机localhost或者127.0.0.1登录

登录查看账户

MySQL数据库的服务端口号是3306，通过在mysql数据库的user表中查看登录数据库用户信息：

mysql> show databases;  # 查看所有的数据库
mysql> use mysql;   # 选择mysql数据库
mysql> show tables;   # 查表数据库中的所有表
mysql> select user, host from user;   # 查看这个表中的user和host信息

删除用户

需要注意的是删除了某个用户之后必须进行权限的刷新：

mysql> delete from user where host="%";   # 删除host为%的用户
Query OK, 1 row affected (0.01 sec)

mysql> flush privileges;  # 刷新权限
Query OK, 0 rows affected (0.00 sec)

注意：当在实际的开发项目中，项目和数据库服务器不再同一个地方，可以指定ip连接进行访问。

mysql> update user set host="192.168.1.10" where user="root";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host from user;

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

当退出mysql重新进入，需要指定IP地址，就是上面设置的IP：

密码问题

1、修改密码

同样需要进入mysql数据库的user表中

mysql>update user set password=PASSWORD("admin")  where user="root";   # 将root账户的密码改为admin

mysql> flush privileges;   # 更新操作
Query OK, 0 rows affected (0.00 sec)

2、忘记密码

如果忘记了密码，需要进入配置文件中

1	ubuntu@peter:~$ vim /etc/mysql/mysql.conf.d/mysqld.cnf

找到下图中的skip-grant-tables，将前面的#去掉，就是取消注释：取消权限认证，后台开启新的进程免密进入MySQL
重启MySQL服务：systemctl restart mysql
通过MySQL直接进入：

然后按照上面的步骤重新设置密码即可

mysql> show databases;
mysql> use mysql;
mysql> select user, password  from user;
# 接下来重新设置密码即可

DQL

DQL（data query language），指的是数据查询语言，主要的作用是对数据库中的数据进行查询的操作，也是最常见和最重要的功能。

查询的方法也是多种多样：联合查询、分组查询、内连查询、子查询等，还可以限制查询的条数等，下面介绍几种常见的查询

格式：

select
	column1,
	column2,...  # 需要查询的字段
from table_name   # 表名
where 条件

简单查询

select
	name,
	age
from user
where id=4;
where id in(1,3,5,7);
where name = "xiaoming";

过滤查询

过滤查询的关键字是distinct，去掉字段中的重复值

1
2
3

-- 过滤重复字段
select distinct(password) from user;     # password是重复项
select distinct password from user;   # 括号可以不用

连接查询

连接查询的关键字是concat

直接使用系统默认的连接方式，将原来的字段通过下划线进行连接
使用concat...as...，as后面自己指定连接的新字段名
带上连接符号的查询concat_ws("+", 列名1，列名2)；其中"+"就是指定连接符

1 2	select concat(name, email) from user; # 结果中显示concat(name_email) select concat(name, email) as nameEmail from user; # 将新的字段名用nameEmail来表示

模糊查询

模糊查询的关键字是like，中文翻译成像：

1
2
3

mysql> select user_name from student where user_name like "peter";    # 像peter
mysql> select user_name from student where user_name like "%e";      # %表示任意，表示名字以e结尾
mysql> select user_name from student where user_name like "%e%";    # 表示名字中含有e

排序查询

对表中的记录进行升序asc或者降序desc的排列，默认的是升序asc，同时需要使用order by关键字：

升序：asc，默认情况
降序：desc

1 2	select * from student order by user_age asc; # 年龄的升序 select * from student order by user_age desc; # 年龄的降序

聚合函数

select count(*) from student;    # 总记录
select sum(列名) from student;  # 总和
select avg(列名) from student;   # 平均值
select max/min(列名) from student;  # 最大/小值

限制查询结果

限制查询的条数使用的是limit关键字

直接使用limit
使用limit ... offset ...：指定从哪里开始显示，显示多少行
简写：limit 5, 4：表示从第5行开始，显示4行数据

1
2
3

select name, age from user limit 5;    -- 只显示5行数据
select name, age from user limit 5 offset 4;   -- 从第4（offset）行开始显示5(limit)行数据
select name, age from user limit 4, 5 ;  -- 效果同上：逗号之前是offset的内容，逗号之后是limit内容

MySQL中的DQL应该是最常用的，请参考《MySQL经典50题》

MySQL必学4种语言

DDL

进入数据库

全部命令

数据库操作

表操作

创建表

查看表结构

查看创建表的SQL语句

字段操作

DML

表中插入数据

数据更新

删除

关于truncate

DCL

全部命令

关于root账户

登录查看账户

删除用户

密码问题

DQL

简单查询

过滤查询

连接查询

模糊查询

排序查询

聚合函数

限制查询结果

查看创建表的`SQL`语句

关于`root`账户