初识mysql

一、数据库的分类

1、关系型数据库：不同表之间的字段可以通过外键联系起来

	Mysql，Oracle，MSSQL，postgreSQL等
1

2、非关系型数据库

MongoDB，Redis，TiDB等
1

二、数据库和表的创建及查看

1、创建数据库

create database tangyuhao;
1

2、查看数据库结构

show create database tangyuhao;
1

3、使用数据库、创建表、查看表结构

1、使用数据库
	use tangyuhao;
2、创建表
	create table nongda(id int,name varchar(20));
3、显示所有表
	show tables;
4、查看表结构
	desc nongda;
5、也是查看表结构，还能看到搜索引擎
	show create table nongda;
1
2
3
4
5
6
7
8
9
10

4、以文件的形式查看数据库里的表

1.db.opt
	存放数据库使用的字符集和校对方法
2.t1.frm
	是t1表的表结构--frame
3.t1.ibd
	是t1表存放数据（索引数据+真正的数据index+data）的文件，因为使用的innodb存储引擎（软件，用来存取数据的，在内存和磁盘之间）
1
2
3
4
5
6

5、根据已有的表创建新表
语法一：

create table new_tbl like orig_tbl
	这种方法只能复制源表的列名，数据类型，大小，非空约束以及索引，不会复制数据，新表是一张空表
1
2

使用

案例：
root@hunan 22:49  mysql>create table sanchuang.user like mysql.user;
	Query OK, 0 rows affected (0.00 sec)

root@hunan 22:50  mysql>use sanchuang;
    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
root@sanchuang 22:50  mysql>show tables;
    +---------------------+
    | Tables_in_sanchuang |
    +---------------------+
    | t1                  |
    | user                |
    +---------------------+
    2 rows in set (0.00 sec)

root@sanchuang 22:50  mysql>select * from user;
	Empty set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

语法二：

create table new_tbl as select column from orig_tbl;
	新表的结构由select列表决定，这个方法不会复制索引，但是获得数据，数据的格式由select确定
1
2

使用

root@sanchuang 22:50  mysql>create table user2 as select user,host from mysql.user;
    Query OK, 4 rows affected (0.01 sec)
    Records: 4  Duplicates: 0  Warnings: 0

root@sanchuang 22:53  mysql>show tables;
    +---------------------+
    | Tables_in_sanchuang |
    +---------------------+
    | t1                  |
    | user                |
    | user2               |
    +---------------------+
    3 rows in set (0.00 sec)

root@sanchuang 22:53  mysql>select * from user2;
    +---------------+-----------+
    | user          | host      |
    +---------------+-----------+
    | liuhongjie    | %         |
    | mysql.session | localhost |
    | mysql.sys     | localhost |
    | root          | localhost |
    +---------------+-----------+
    4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

三、mysql里的字符集

1、查看mysql里面的字符集设置

1.show character set;
2.show variables like "%character%";
1
2

2、如何知道某个库用的什么字符集

show create database tangyuhao;
	tangyuhao是一个数据库
1
2

3、字符集的继承关系

mysql服务器《----库《----表《----列
1

4、修改数据库使用的字符集

create database chen default charset=utf8mb4;
1

四、锁

1、表锁

读锁：read   --》其他事务可以读，不能写（共享锁）
写锁：write   --》其他事务不能读，也不能写（排他锁）
1
2

2、行锁（概念和表锁一样，只是颗粒度不相同，精确到行）
3、活锁：在竞争资源的时候，一直抢占不到资源，处于等待中，活活锁死
4、死锁：得到资源需要满足2个条件，A进程和B进程各占一个，互相不谦让，一直僵持

如何避免死锁
	1、设计流程，必须拿到第1个条件，才能拿第2个条件
	2、设计一个单独的进程，去检查是否发生死锁，如果发生了，杀死一个，释放资源
1
2
3

5、互斥锁
6、悲观锁
7、乐观锁

五、系统调优

1.升级硬件
2.系统（linxu）调优：内存，文件系统，内核等参数优化
3.mysql的调优

参数的调优--》需要了解mysql的机制
sql语句的调优--》需要了解sql语句
1
2

4.架构的调优

中间件（缓存）
分布式（集群）
1
2

六、mysql里的实例

1、mysql实例：正在运行的一个mysql进程，整个进程里有哪些库可以操作，二进制日志就记录哪些库的操作
2、多实例

1、多启动几个mysqld的进程，一个mysqld的进程对应一个库
2、隔离应用，避免一个库使用特别频繁，影响其他的库
3、多实例仍然受到整个机器整体系统资源的限制。
4、经典案例：
	一款游戏对应一个实例
1
2
3
4
5

七、shell命令操作数据库

1、-e选项，后面接mysql语句

mysql -uroot -p123456 -e "show databases;use sanchuang;show tables;select * from user2"
1

2、here document

cat con_mysql.sh 
    #!/bin/bash
    mysql -uroot -p123456 <<EOF
    show databases;
    use sanchuang;
    show tables;
    select * from user2
    EOF
    echo $?
    echo "查询数据成功"
1
2
3
4
5
6
7
8
9
10

3、shell清空表的语句

1、delete from student；
2、truncate table student；
1
2

delete和truncate的区别？
	1.delete删除表里的数据的时候是一行一行的删除，删除后会产生二进制日志，可以根据日志恢复。
	2.truncate删除数据特别快，整个表锁定操作然后删除，不会记录二进制日志，不能恢复，删除大表
1
2
3

4、练习：

在test数据库里面，除了指定的表里liu1,liu2,liu3的数据不能动以外，其他的表里的数据全部清空，使用shell脚本实现
		#!/bin/bash
	#过滤掉不想清除数据的表，赋值给变量
	drop_table=$(mysql -uroot -p'123456' -e "use test;show tables;" 2>/dev/null|tail -n +2 |egrep -v "liu1|liu2|liu3")
	#for循环配合truncate语句，清空表数据
	for i in $drop_table
	do
		mysql -uroot -p123456 -e "use test;truncate table $i" 2>/dev/null
		echo "清除$i数据成功"
	done
1
2
3
4
5
6
7
8
9
10