迈向MySql的第一步---- 基本语法
关于库
进入数据库
[root@localhost ~]# mysql> mysql -uroot -p123
查看所有的数据库
mysql> show databases;
mysql> show databases \G
创建数据库
mysql> create database wangyu;
#或者加上反撇号
mysql> create database `wangyu`;
使用(进入)某个数据库
mysql> use wangyu;
Database changed
查看所处位置
mysql> select database();
+------------+
| database() |
+------------+
| wangyu |
+------------+
删除库
mysql> drop database wangyu;
关于表
创建表
mysql> create table student(name char(20),id int(10),age int(3));
查看当前库的所有表
mysql> show tables;
查看一下表的结构
mysql> desc student;
修改表字段的类型
modify
mysql> alter table students modify name char(5);
change
mysql> alter table students change name names char(5);
注意,modify和change的区别
modify只能修改字段的类型,change可以修改字段的名字也可以修改他的字段类型。
添加字段
mysql> alter table students add sex int(1);
#添加到指定位置
mysql> alter table students add sex1 int(1) after name;
删除字段
mysql> alter table students drop sex1;
数据的CRUD
插入一条数据 insert into
mysql> insert into students values('王宇',2,21);
删除一条数据 delete
mysql> delete from students where sid=2;
修改数据 update
mysql> update students set age=23 where sid=3;
查看数据 select
#查看一张表的所有信息
mysql> select * from students;
#查看指定内容
mysql> select name from students;
#给查询结果返回的字段起别名
mysql> select name as '姓名' from students;
#条件查询
mysql> select * from students where sid=1;
条件查询
范围查询 and
#查询年龄在30岁到40岁之间的人
mysql> select * from students where age>=30 and age<=40;
#查询学号在20岁到30岁之间的人的姓名
mysql> select name from students where age>=20 and age<=30;
范围查询 or
#查询年龄大于50或者小于20的人
mysql> select * from students age<20 or age>50;
使用between…and… 实现范围查询
#查询年龄在30岁到40岁之间的人
mysql> select * from students where age between 30 and 40;
使用in
#查询年龄27岁或者34岁的人的姓名
mysql> select name from students where age in (27,34);
查询去重 distinct
#去除重复的信息
mysql> select distinct * from students;
排序 order by
#对年龄排序 降序
mysql> select * from students order by age desc;
##对年龄排序 升序
mysql> select * from students order by age asc;
#默认情况时升序
mysql> select * from students order by age ;
基本语法综合练习
#创建名字为test的库
#在test库中创建students表,它的字段为sid整型,name字符型,age整型。
#自己插入10条数据
#把sid为6的人的年龄改为88
#删除sid为4的人
#查询sid在 2~5 之间(包含2和5)的人,将他们的年龄按照降序排序,并且去除相同的人。
2、mysql数据类型
- 数值类型
- int
- bigint
- double
- float
- 日期类型
- date:年月日
- time:时分秒
- year:年
- datatime:年月日+时分秒
- 字符串类型
- char:范围时0-255
- varchar:0-65535
- text:文本
- 枚举
3、Mysql语法进阶
模糊查询 Like
#查询姓名以li开头的人
mysql> select * from students where name like 'li%';
#查询姓名包含li的人
mysql> select * from students where name like '%li%';
查看Mysql的运行状态
mysql> show status;
查看进程目录
mysql> show processlist;
数据库的导出 mysqldump
[root@localhost ~]# mysqldump -B -uroot -p123 wangyu>wangyu.sql
数据库导入
- 在库中导入
mysql> source /root/wangyu.sql
- 在linux命令行导入
[root@localhost ~]# mysql -uroot -p123 wangyu < wangyu.sql
嵌套查询
#查询bId为1的书对应书类型的类型名称
mysql> select bTypeName from category where bTypeId
in (select bTypeId from books where bId =1);
#查询网站类型书对应的所有图书的名称
mysql> select bName from books where bTypeId
in (select bTypeId from category where bTypeName='网站');
限定数目查询 limit A,B
- A代表偏移量
- B代表查找的数目
#查询书记种类前三条数据
mysql> select * from category limit 0,3;
#查询书籍种类从第三条开始之后的4条数据
mysql> select * from category limit 2,4;
综合应用
#找出电子工业出版社出版的价格最便宜的书
mysql> select bName,price from books
where publishing = '电子工业出版社' order by price asc limit 0,1;
#找出比电子工业出版社出版的价格最便宜的书还要贵的书
mysql> select * from books where price>(select price from books where publishing = '电子工业出版社' order by price asc limit 0,1)
多表连接查询
- 内连接 inner join on
- 外连接
- 左外连接 left join on
- 右外连接 right join on
内连接
#查询bId为1的书对应书类型的类型名称
mysql> select bName,bTypeName
from books join category c on books.bTypeId = c.bTypeId
where bId = 1;
左外连接和右外连接的区别
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5lHXd7sn-1643852074351)(C:\Users\77259\AppData\Roaming\Typora\typora-user-images\1630830516229.png)]
聚合函数
- Sum()
#求所有书的价格总和
mysql> select sum(price) as '总和' from books;
- AVG()
#求所有书的价格的平均数
mysql> select avg(price) as '平均' from books;
- MAX()
#找出所有书中最贵的数
mysql> select max(price) as '最大' from books;
- MIN()
#找出所有书中最便宜的数
mysql> select max(price) as '最小' from books;
4、字段约束
NULL 与 NOT NULL
NULL:可以为空
NOT NULL:不可以为空
#创建测试表
mysql> create table test(id int(11) not null,name char(11) null);
#都插入数值
mysql> insert into test values(1,'wangyu1');
Query OK, 1 row affected (0.00 sec)
#只插入id
mysql> insert into test (id) value(2);
Query OK, 1 row affected (0.00 sec)
#只插入name
mysql> insert into test (name) value('wangyu2');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
DEFAULT
default设置字段默认值
mysql> create table test2(name varchar(8) not null,dept varchar(25) default 'SOS');
mysql> insert into test2 (name) values('wang');
mysql> insert into test2 values('wang','SSS');
AUTO_INCREAMENT
设置自动增长
索引
索引就像是一本书的目录,我们可以快速的查找到我们想要的信息。
优点:加快搜索速度,减少查询时间 。
缺点:增加了存储需要的空间,增加了增删改的任务操作。
- 普通索引
- 唯一索引
- 主键索引
- 复合索引
创建索引 index key
#使用index
mysql> create table demo(id int(11),name varchar(20),index (id));
#使用key
mysql> create table demo(id int(11),name varchar(20),key (id));
给已有的表添加索引
#给id字段增加索引
mysql> alter table items add key(id);
创建唯一索引
mysql> create table demo3(id int(4) auto_increment primary key, uName varchar(20) unique, uPwd varchar(20), index (id));
创建主键索引
mysql> create table demo5( id int(4) not null auto_increment primary key, name varchar(20) default null);
创建符合索引
mysql> create table firewall ( host varchar(15) not null ,port smallint(4) not null ,access enum('deny','allow') not null, primary key (host,port));
创建索引的原则
- 索引并非越多越好,每个额外的索引都要占用额外的磁盘空间,并降低增、删、改操作的性能,因此,索引越多,所花的时间越长。
- 数据量不大不需要建立索引
- 在where子句中出现的字段、常排序(order
by 字段)和分组(group
by 字段)的列需要建立索引 - 唯一性约束对应使用唯一性索引
- MySQL只对以下操作符才使用索引:<,<=,=,>,>=,between,in,
以及某些时候的like(不以通配符%开头的情形)。
外键约束 foreign key
创建主表user
mysql> create table `user`(id int(11) not null auto_increment, name varchar(50) not null default '', sex int(1) not null default '0', primary key(id))ENGINE=innodb;
创建从表oder
mysql> create table `order`(o_id int(11) auto_increment, u_id int(11) default '0', username varchar(50), money int(11), primary key(o_id), index(u_id), foreign key order_f_key(u_id) references user(id) on delete cascade on update cascade) ENGINE=innodb;
参数说明
- foreign key :指定本表中的外键
- references:指定它是谁的外键
- on delete cascade on update cascade:表示级联操作,主表删除修改时从表也会发生相应的改变。
测试
mysql> insert into user values(1,'小明',0);
mysql> insert into user values(2,'小红',1);
mysql> insert into `order` values(1,1,'HA',300);
mysql> update user set id=3 where name='小明';
mysql> select * from user;
mysql> select * from `order`;
5、事务
Mylsam引擎不支持事务,Innodb引擎支持事务
事务的四大特性ACID
- 原子性
- 一致性
- 隔离性
- 持久性
# START TRANSACTION | BEGIN 开启事务
# commit 提交事务
# ROLLBACK 回滚事务
#MYSQL默认是自动提交的,也就是你提交一个SQL QUERY,它就直接执行!
测试提交
mysql> set autocommit=0; //关闭自动提交
mysql> delimiter //
mysql> start transaction;
-> update books set bName="ccc" where bId=1;
-> update books set bName="ddd" where bId=2;
-> commit; //
mysql> delimiter ;
测试回滚
#修改表的引擎
alter table books engine=innodb;
alter table category engine=innodb;
6、mysql日常备份工具
7、mysql优化
调优思路
- 硬件优化
- 磁盘优化
- 操作系统优化
- 纵向优化、横向优化
- 数据库设计
- my.cnf参数优化
- mysql查询优化
- Mysql存储引擎
查询优化
- 启用Mysql的慢查询日志,找到比我们设置的时间还要长的语句
mysql> show global variables like '%slow_query_log%';
+---------------------+-------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /data/mysql/data/localhost-slow.log |
+---------------------+-------------------------------------+
解释参数
slow_query_log: off关闭状态 on开启状态
slow_query_log_file:慢查询日志存放地点
mysql> show global variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name | Value |
+----------------------------------------------------------+-----------+
| long_query_time | 10.000000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
+----------------------------------------------------------+-----------+
long_query_time:选项来设置一个时间值,时间以秒为单位,可以精确到微秒。如果查询时间超过了这个时间值(默认为10秒),这个查询语句将被记录到慢查询日志中, 设置为0的话表示记录所有的查询。
开启慢查询日志功能
方法一:
#修改配置文件
[root@localhost etc]# vim /etc/my.cnf
#添加一下内容
slow_query_log = 1 #开启慢查询日志
slow-query-log-file=/data/mysql/log/slow.log #这个路径对 mysql 用户具有可写权限
long_query_time=1 #查询超过1秒钟的语句记录下来
log-queries-not-using-indexes =1 #没有使用索引的查询
方法二:
mysql>set global slow_query_log=1; #开启慢查询日志
mysql>set global long_query_time=0.001; #更改时间
- 使用计划任务 explain
mysql> explain select bName from books where bId=1 or bId=2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: books
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.01 sec)
- 当只要一行数据时使用LIMIT 1
- 只取自己需要的column,避免使用SELECT *
- 添加索引(主键索引/唯一索引/普通索引/复合索引)
- 不做列运算
- sql语句尽可能简单:一条sql只能在一个cpu运算;大语句拆小语句,减少锁时间;一条大sql可以堵死整个库。
- OR改写成IN
- 避免 like %xxx式查询
- 尽量避免在WHERE子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描
存储引擎
- myisam存储引擎
- 表结构定义信息的.frm文件
- 表的数据(.MYD)
- 索引数据(.MYI)
- myisam存储引擎特点
- l 表级锁,读取时对需要读到的所有表加锁,写入时则对表加排它锁
- l 数据库在读写过程中相互堵塞,在数据写入的过程中阻塞用户对数据的读取,在数据读取的过程中阻塞用户写入数据。
- l 表级锁开销小,影响范围大,适合读多写少的表
- 不支持事务
- l 不支持外键
- l 不支持崩溃后的安全恢复
- Innodb存储引擎
- .frm文件来存放表结构定义相关的元数据
- .ibd 表数据和索引数据是存放在一起的
- Innodb存储引擎的特点
- l 行级锁,锁定行的开销要比锁定全表要大。影响范围小,适合写操作比较频繁的数据表。但是全表扫描仍然会是表级锁定
- l 支持事物,支持四个事务隔离级别
- l 支持外键
- l 支持崩溃后的安全恢复
- sql语句尽可能简单:一条sql只能在一个cpu运算;大语句拆小语句,减少锁时间;一条大sql可以堵死整个库。
- OR改写成IN
- 避免 like %xxx式查询
- 尽量避免在WHERE子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描
存储引擎
- myisam存储引擎
- 表结构定义信息的.frm文件
- 表的数据(.MYD)
- 索引数据(.MYI)
- myisam存储引擎特点
- l 表级锁,读取时对需要读到的所有表加锁,写入时则对表加排它锁
- l 数据库在读写过程中相互堵塞,在数据写入的过程中阻塞用户对数据的读取,在数据读取的过程中阻塞用户写入数据。
- l 表级锁开销小,影响范围大,适合读多写少的表
- 不支持事务
- l 不支持外键
- l 不支持崩溃后的安全恢复
- Innodb存储引擎
- .frm文件来存放表结构定义相关的元数据
- .ibd 表数据和索引数据是存放在一起的
- Innodb存储引擎的特点
- l 行级锁,锁定行的开销要比锁定全表要大。影响范围小,适合写操作比较频繁的数据表。但是全表扫描仍然会是表级锁定
- l 支持事物,支持四个事务隔离级别
- l 支持外键
- l 支持崩溃后的安全恢复