第12章 MySQL基础语句

SQL语句结构

1.数据查询语言（DQL：Data Query Language）
也称为“数据检索语句”，包括SELECT , WHERE , ORDER BY ,GROUP BY ,HAVING

2.数据操作语言（DML：Data Manipulation Language）
包括动词INSERT,UPDATE,DELETE，它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

3.事务处理语言（TPL）：由多条SQL语句组成的整体。
它的语句能被确保被DML语句影响的表所有行为及时得到更新。TPL语句包括BEGIN TRANSACTION COMMIT ROLLBACK

4.数据控制语言（DCL）
通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

5.数据定义语言（DDL）
包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE或DROP TABLE）；为表加入索引等。

6.指针控制语言（CCL）
它的语句，想DECLARE CURSOR， FETCH INTO ，UPDATE WHERE CURRENT 用于对一个或多个表单执行的操作。

MySQL语句 

1.查看数据库 

mysql -u root -p123456 -h127.0.0.1

show databases;
查看所有数据库

showdatabases\G
以行的方式显示

exit
退出数据库

mysql -e 'show databases' -uroot -p123456
mysql-e 可以直接在终端运行，免交互执行，用于shell脚本

2.创建数据库

create database 数据库名;
数据库名必须遵循目录名约束，数据库名不得重名，数据库名不得超过64个字符，包含特殊字符或全部由数字及保留字组成的名字必须用反引号包含。例如create database `ha-test` , create database `select`

3.选择要操作的数据库

use mysql;
选择要操作的数据库

4.查看自己所处的位置及默认所在位置

select database();
查看当前选择要操作的数据库是哪个，就是use了哪个数据库。

5.在命令行选择默认的数据库

mysql -uroot -p123456 HA
select now()user(),database();

6.删除数据库：删除没有提示， 谨慎操作

方法1，在MariaDB数据库中使用drop命令删除库。
drop database `ha-test`;
方法2，数据库就是一个文件夹，把文件夹转移到其它位置,回到MariaDB中show databases就不存在了。
cd /var/lib/mysql/
mv ha@002dtest /opt/
方法3，使用if exists 子句删除或创建
drop database if exists `ha-test`;
如果存在则删除
create database if not exists ha;
如果不存在则创建，如果存在则不创建

关于表的操作

1.查看库有哪些表，查看表时，要进入到数据库再查看

use mysql；

show tables;

2.创建表

create table 表名(字段名 类型，字段名 类型，字段名 类型);
create database school;
use school;
create table student(id int(20),name char(40),age int(11));

3.查看表结构

desc student；

4.查看创建表执行了哪些命令

show crate table student\G
create table student2(id int(20),name char(40),age int)ENGINE=MyISAM DEFAULT CHARSET=utf8;
show create table sutdent2\G

5.删除表

drop table student2;

6.禁止预读取表信息

mysql -uroot-p123456 -A

禁止前

禁止后

information_shema 
这库保存Mysql服务器所有数据库的信息，如数据库名，数据库的表，表栏的数据类型及权限等。
performance_schema
这库用于收集数据库服务器性能参数。
mysql
这库是系统库，里面保存有账户信息，权限信息等。

7.修改表名称

alter table 表名 rename 新表名；
use school;
alter table student rename student2;
show tables;

8.修改表中的字段类型

alter table 表名 modify 字段名 类型(长度)；
alter table student modify id int(10)；

9.修改表中的字段类型和字段名称

alter table 表名 change 原字段名 新字段名 新字段类型(长度)；
alter table student change name newname varchar(255);

10.在表中添加字段

alter table 表名 add 字段名 字段类型；
alter table student add sex enum('M','W');

11.指定位置添加字段

在第一列添加一个字段
alter table student add uid int(10) first;

在age后面添加一个address字段
alter table students add address char(40) after age;

12.删除表中字段

alter table 表名 drop 字段名；
alter table student dop address;

关于记录的操作

1.插入记录 insert

insert into 表名 values (字段值1，字段值2，字段值3)；
insert into 表名 (字段名) value (字段值);
insert into test2 (name) value ('jack');
insert into student values(1,'libai',21,'china',W);
insert into student values(1,'hanxin',22,'china','W'),(1,'guanyu','33','','');

2.查询表中记录

select * from 表名\G；
从表中查询所有字段内容，*表示所有字段

select name,age from student;
只查询指定字段内容

3.查看非当前数据库中的表

select host,user from mysql.user;
查询其它数据库中的表,要写库名.表名，等效于先执行use 库名

4.删除记录行

delete from student where id=3;
删除id为3的行

delete from student where age is null;
删除age为空的行

5.更新记录

update student set name='M' where id=2;
更新student表中id为2的行，把其中name字段的值更新为M
update student set id=2 where name='libai';
更新student表中name为libai的行，把其中id字段的值更新为2
update student set id=2;
所有行id字段的值都更新为2

SQL基础条件查询语句

1.select 字段名1，字段名2 from 表名 where 条件;

select id,name,age from student where address='china';
查询学生表，地址是中国的，id，name，age字段

2.去重复查询

select distinct name,age from student;
赵云和赵云，都是22岁，一个是男，一个是女，这里只显示1个赵云。
select distinct name,age,sex from student;
赵云和赵云，都是22岁，一个是男，一个是女，这里会显示2个赵云。
select distinct * from student;
查询student表中，所有字段的值都相同的记录只显示一次。

3.使用and和or进行多条件查询

当and和or同时存在时，先执行and

select id,name,age from student where id>1 and age>24;
查询student表中为id字段值大于1且age字段值大于24的记录，并显示id，name，age字段的值。

select id,name,age from student where id>1 or age>24;
查询studnet表中id字段值大于1或者age字段值大于24的记录，并显示id,name,age字段的值。

select * from student where name='zhaoyun' and (age=21 or age=22);
查询student表中name字段值为zhaoyun且age字段值为21或22的记录，并显示全部字段的值。

4.MySQL区分大小写查询

MySQL查询是不区分大小写的，使用binary类型转换运算符， 强制它后面的字符串为一个二进制字符串，用来字符串比较时区分大小写。

select * from student where  name='ZHAOyun';

select * from student where binary name='ZHAOyun'；

5.MySQL查询排序

select  字段1，字段2 from 表名 order by 字段名；
默认为升序 asc，可以设置为降序desc。

select distinct id from student order by id asc;
select distinct id from student order by id desc;

6.Mysql命令帮助 

help show；
help select；

实战：升级MySQL版本到5.7版本并迁移数据

1.备份数据

mysqldump -u root -p123456 -B 迁移库名 > /备份目录/导出名称.sql
mysqldump -uroot -p123456 -B testbase > /backdata/testbase_back.sql

2.删除旧版本mariadb

systemctl stop mariadb
yum -y remove mariadb*

3.yum安装MySQL 5.7的方法

https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/
rpm -ivh https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm

此时 ls /etc/yum.repos.d/ 会多出2个文件
mysql-community.repo  mysql-community-source.repo

yum list
yum -y install mysql-commuity-server

下载需要一定时间，如果觉得慢可以通过迅雷下载所有安装包，rz上传到linux某目录中。

通过yum安装，我们知道了需要安装的依赖包的名字和大小。

鼠标右键，这样下载比较方便

下载的所有包上传到linux中某目录内，确保进入安装包所在目录，然后使用rpm -ivh mysql-community-* 来安装或yum localinstall *.rpm 安装

 systemctl start mysqld
启动服务，会生成临时密码
grep "password" /var/log/mysqld.log
第一次通过grep来获取mysql的临时密码
登录mysql
mysql -uroot -p'buL.UJp!T2oD'  #注意临时密码要单引号
登陆后第一时间修改密码，如果设置简单密码，则把validate_password_policy的值改为0
set global validate_password_policy=0; 0 只检查长度 1 检查长度，数字，大小写，特殊字符 2 检查长度，数字，大小写，特殊字符字典文件
set global validate_password_length=1; length默认是8位数，改为1，则长度大于等于4位数
ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 42. Created with MySQL 50560, now running 50725. Please use mysql_upgrade to fix this error.
升级过数据库，升级后没有升级数据结构解决办法: 回到linux中执行 mysql_upgrade -u root -p123456
ERROR 1193 (HY000): Unknown system variable 'validate_password_policy'
保存系统中不存在此变量
SHOW VARIABLES LIKE 'validate_password%';
ERROR 1682 (HY000): Native table 'performance_schema'.'session_variables' has the wrong structure
解决办法:set @@global.show_compatibility_56=ON;
 

修改密码
alter user 'root'@'localhost' IDENTIFIED BY '1234';
或
set password for root@localhost=password('123456');

 方法2：在/etc/my.cnf可关闭密码强度审计插件，重启mysql服务
在[mysqld]末行追加
validate-password=OFF  #不使用密码强度审计插件。
systemctl restart mysqld

导入数据库
mysql -uroot -p123456 < /backdata/testbase_back.sql
mysql -uroot -p123456
show databases;

 

mysql -e 'create database 数据库名' -uroot -p123456
mysql -uroot -p123456 book < book.sql

use book;
source /root/book.sql

mysqldump -u root -p123456 -B 迁移库名 > /备份目录/导出名称.sql
-B 导出整个库包含建库语句
-A 导出全部数据库