poetry

世味年来薄似纱,谁令骑马客京华。
小楼一夜听春雨,深巷明朝卖杏花。
矮纸斜行闲作草,晴窗细乳戏分茶。
素衣莫起风尘叹,犹及清明可到家。

Init初始

安装和创建授权用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 查看是否已经安装
rpm -qa mariadb

# 安装mysql和服务端
yum -y install mariadb-server mariadb

# 开启mysql进程
systemctl start mariadb

# 初始化mysql,主要是创建root用户的密码,如果没有这步,root密码为空
mysql_secure_installation

# 创建用户
create user 'username'@'localhost' IDENTIFIED BY 'userpasswd';

# 授权用户
grant all privileges on *.* to 'username'@'localhost';

# 创建用户并授权,并授权指定数据库下所有表,而非所有数据库
grant all on dbname.* to 'username'@'localhost' identified by 'userpasswd';

# 删除用户
drop user 'username'@'localhost';

# 刷新mysql系统权限相关表
flush privileges;

# 显示当前用户
select user();

# 显示所有用户
select User, Host, Password FROM mysql.user;

DB_SQL

数据库的库语句,包括数据库的创建,删除,字符集等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 查看当前MySQL中有哪些数据库
show databases;

# 查看选中数据库详情
show create database <db_name>\G

# 创建数据库
create databases <db_name>;

# 使用数据库
use <db_name>;

# 删除数据库
drop database <db_name>;

# 设置字符集
create database <db_name> default charset 'utf8';

TB_SQL

表语句包括创建表、删除表、查看表以及定义表中字段的属性。其中定义表中字段属性往往比创建表更值得研究。

表语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 首先还是解决一下创建表时常见的字符集问题
create table tableName
(...)DEFAULT CHARSET=utf8mb4;

# 创建表
create table tb_name(...);

# 查看表
show tables;
show table status like '<tb_name>'\G

# 删除表
drop tables <tb_name>;

# 修改表
alter table <old_name> rename <new_name>

实例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [demo]> DESC myt7;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sex | char(10) | YES | | man | |
+-------+----------+------+-----+---------+----------------+

/*
NULL:是否可以存储NULL值
Key:是否已经编制索引,PRI表示该列是主键的一部分;UNI表示该列是UNIQUE索引的一部分,NULL表示在列中某个给定值允许出现多次
Default:表示该列是否有默认值
Extra:获取该列的附加信息
*/

实例

1
2
3
4
5
6
7
8
//查询后删除表
MariaDB [demo]> select group_concat(table_name) from information_schema.tables where table_schema='demo';
+----------------------------+
| group_concat(table_name) |
+----------------------------+
| fruits,sping,suppliers,x,y |
+----------------------------+
drop tables fruits,sping,suppliers,x,y;

字段

1、SQL主键的六种约束

名称 含义
PRIMARY KEY 主键约束,记录唯一标识,主键自动拥有not null及 unique约束
NOT NULL 非空约束,强制字段有值
UNIQUE 唯一约束,该字段值必须唯一
FOREIGN KEY 外键,预防破坏表之间的连接行为,防止非法数据插入外键列
CHECK 限制列的取值范围
DEFAULT 设置默认值
AUTO_INCREMENT 自动增加字段,且字段类型必须为整型主键一部分

实例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
1
create table my1(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL UNIQUE ,
sex char(2) NOT NULL DEFAULT 'm'
);

2
//主键约束
create table myt3(
name VARCHAR(50),
salary FLOAT,
PRIMARY KEY(name,salary)
);

3
//外键约束
/*
外键表:即当前这张表
主键表:mainTable
绑定的主键:main_key_name
绑定的外键:foreign_key_name
绑定名称:foreign_name
constraint : 约束
*/
constraint (foreign_name) foreign key(foreign_key_name) references mainTable(main_key_name)

修改字段语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 修改字段类型和字段名
alter table <tb_name> change <old_field> <new_field> INT(15);

# 修改字段的类型和字段名
alter table <tb_name> add <new_field> INT(15) AFTER|FIRST <field>;

# 删除字段
alter table <tb_name> drop <field>;

# 修改字段的排列位置
alter table <tb_name> modify <f1> type FIRST|AFTER <f2>;

# 修改字段的数据类型
alter table <tb_name> modify <field> INT(20);

# 更改存储引擎
alter table <tb_name> engine=<en_name>;

# 删除外键约束
alter table <tb_name> drop FOREIGN KEY 外键约束名;

# 删除没有关联的表
drop table IF EXISTS <tb1>,<tb2>;

总结:

change用来修改KEY本身

modify则修改KEY的属性

后面直接和定义语法一样,千万别忘了后面的类型,如…INT(11),不然会报错

FIRST:将字段设置为表的第一个字段

AFTER:将新添加的字段添加到指定字段的后面

实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
删除两个有关联的表
create table pt(
id int(11) PRIMARY KEY,name varchar(30)
);
create table subt(
id int(11) PRIMARY KEY,
depId int(11),
CONSTRAINT fk_name FOREIGN KEY (depId) REFERENCES pt(id)
);
/*
不能直接 drop pt,但可以直接删除subt
先解除关联,再删除主表
*/
alter table subt drop FOREIGN KEY fk_name;
drop table pt;

SQL

insert

1
2
3
4
5
6
7
8
9
10
# 两种数值插入的方式
insert into <tb_name> (coloumn_list) VALUES (value_list);
insert into <tb_name> VALUES (value_list);

# 将查询到的结果插入
insert into table1 (coloumn_list) select (coloumn_list) from table2 WHERE (condition);

for example
MariaDB [demo]> insert into my1
-> select id,name,age from my2 WHERE id>2;

update

1
update <tb_name> SET column_name1=value1,column_name2=value2,.... WHERE condition;

delete

1
2
DELETE from <tb_name> [WHERE condition];
/如果没有WHERE,则删除表中所有记录。

NB.select

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select  字段1,字段2,字段3...
-> from [表1],[表2]... //数据来源
-> WHERE 表达式 //查询的条件
-> GROUP BY (group by definition) //如何显示查询数据,按照指定字段分组
-> HAVING (expression) [(operator) (expression)]
-> ORDER BY (order by definition) //用什么样的顺序显示查询的数据,ASC(升序)、DESC(降序)
-> LIMIT [(offset),] (row count) //限制显示条数

select {字段1,字段2,字段3,...}
from {表或视图}
WHERE {查询条件};

过滤,排序,分组。
排序要在过滤之后,不然报错

数据源

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 创建库
create database db_test default charset 'utf8';
# 创建表
create table person
(
id INT NOT NULL AUTO_INCREMENT,
name CHAR(20) NOT NULL,
book VARCHAR(50) NOT NULL,
dynasty VARCHAR(10) DEFAULT 'China',
PRIMARY KEY(id)
)DEFAULT CHARSET=utf8mb4;
# 插入值
insert into person (name,book,dynasty) VALUES ('王阳明','传习录','ming');
insert into person (name,book) VALUES ('林清玄','白雪少年');
insert into person (name,book,dynasty) VALUES ('孔子','论语','chunqiu');
insert into person (name,book,dynasty) VALUES ('老子','道德经','chunqiu');
insert into person (name,book,dynasty) VALUES ('李白','蜀道难','tang');
insert into person (name,book,dynasty) VALUES ('陈子昂','登幽州台歌','tang');
# 查看插入的值
select * from person;
image-20200302153805396

简单查询

1
2
3
4
5
6
MariaDB [nikitest]> select id,name,book,dynasty from person where id in (01,02,06) AND dynasty='tang';
+----+-----------+-----------------+---------+
| id | name | book | dynasty |
+----+-----------+-----------------+---------+
| 6 | 陈子昂 | 登幽州台歌 | tang |
+----+-----------+-----------------+---------+

别名查询

1
2
3
4
5
6
7
8
9
10
11
MariaDB [nikitest]> select id as ID,name as NAME,book as BOOK,dynasty as DYNASTY  from person as f where f.id < 5;
+----+-----------+--------------+---------+
| ID | NAME | BOOK | DYNASTY |
+----+-----------+--------------+---------+
| 1 | 王阳明 | 传习录 | ming |
| 2 | 林清玄 | 白雪少年 | China |
| 3 | 孔子 | 论语 | chunqiu |
| 4 | 老子 | 道德经 | chunqiu |
+----+-----------+--------------+---------+

//即将字段以别名显示,同时将表也以表明显示(这里用的是f->person,所以条件查询f下的字段就可以写成f.id)

排序

1
2
3
4
5
6
//单列排序,DESC反序
select DISTINCT id,name,book, dynasty from person order by id DESC;

//多列排序,先按照朝代排序,相同朝代的再按照名称排序
select DISTINCT id,name,book, dynasty from person order by dynasty DESC,name;

分组

1
2
3
4
5
6
7
8
9
10
//同类分为一组,通过GROUP_CONCAT(field)显示详细信息
> select group_concat(name),dynasty from person group by dynasty;
+--------------------+---------+
| GROUP_CONCAT(name) | dynasty |
+--------------------+---------+
| 林清玄 | China |
| 孔子,老子 | chunqiu |
| 王阳明 | ming |
| 李白,陈子昂 | tang |
+--------------------+---------+

过滤

1
2
3
4
5
6
7
8
9
//查看同一时代的人,过滤出人数>1的
> select group_concat(name), dynasty from person group by dynasty having count(dynasty)>1;
+--------------------+---------+
| group_concat(name) | dynasty |
+--------------------+---------+
| 孔子,老子 | chunqiu |
| 李白,陈子昂 | tang |
+--------------------+---------+

限制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
//限制显示结果为 offset & count
> select * from person limit 2;
+----+-----------+--------------+---------+
| id | name | book | dynasty |
+----+-----------+--------------+---------+
| 1 | 王阳明 | 传习录 | ming |
| 2 | 林清玄 | 白雪少年 | China |
+----+-----------+--------------+---------+
2 rows in set (0.00 sec)

//限制显示2个结果,并从第4个开始显示,这里设置了偏移位=4
> select * from person limit 3,2;
+----+--------+-----------+---------+
| id | name | book | dynasty |
+----+--------+-----------+---------+
| 4 | 老子 | 道德经 | chunqiu |
| 5 | 李白 | 蜀道难 | tang |
+----+--------+-----------+---------+

AF函数

aggregate function,mysql内置聚合函数

函数 作用
AVG() 列数值均值
COUNT() 个数,忽略空行,COUNT(*)计算表中总行数,不忽略空行
MAX() 列数值最大
MIN() 列数值最小
SUM() 列数值和

实例

1
2
3
4
5
6
select id,name,book,MAX(dynasty) from person;

| 0111 0100 | 0164 | 116 | 0x74 | t |
| 0110 0011 | 0143 | 99 | 0x63 | c |
| 0110 1101 | 0155 | 109 | 0x6D | m |
//这里t最大,所以选出来tang

这里只介绍了基础的SQL语句,后面还有连接查询、子查询、合并查询等……..