mysql（五）
2019-11-29 本文已影响0人
Freestyle_0f85
mysql管理命令
mysql> status #查看数据库的状态（\s）
mysql> exit quit # 退出数据库（\q）
mysql> \c #终止当前的SQL语句
mysql> help #查看帮助信息（\h、？、\?）
mysql> source #导入数据（\.）
mysql> use #切换数据库（\u）
mysql> tee /tmp/a.log #记录操作日志（\T）
mysql> show slave status\G #将结果展示成key:value的形式
mysqladmin管理命令
#设置密码 password
[root@db01 ~]# mysqladmin -uroot -p1 password '123'
#关闭实例 shutdown
[root@db01 ~]# mysqladmin -uroot -p1 shutdown
#创建数据库 create
[root@db01 ~]# mysqladmin -uroot -p123 create oldboy2
#删除数据库 drop
[root@db01 ~]# mysqladmin -uroot -p123 drop oldboy2
#查看MySQL的参数 variables
[root@db01 ~]# mysqladmin -uroot -p123 variables
#刷新binlog日志 flush-log
[root@db01 ~]# mysqladmin -uroot -p123 flush-log
#重载授权表 reload
[root@db01 ~]# mysqladmin -uroot -p123 reload
#判断MySQL是否存活
[root@db01 ~]# mysqladmin -uroot -p123 ping
清空表数据
删除表信息的方式有两种 :
truncate table table_name;
delete * from table_name;
注 : truncate操作中的table可以省略，delete操作中的*可以省略
truncate、delete 清空表数据的区别 :
1> truncate 是整体删除 (速度较快)，delete是逐条删除 (速度较慢)
2> truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete高的原因
什么是sql语句
机构化查询语句
1.DDL(data definition language)
数据定义语言
针对库的DDL
#增（create）
mysql> create database db;
mysql> create schema db1;
mysql> create schema if not exists db1;
mysql> create database db2 charset utf8 collate utf8_general_ci;
#删（drop）
mysql> drop database zls1;
#改（alter）
mysql> alter database zls charset gbk;
##DQL ，查看建库语句（字符集）
mysql> show create database zls;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| zls | CREATE DATABASE `zls` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
针对表的DDL
#增
## 数据类型
整型
int -2^31 ~ 2^31-1
tinyint -128 ~ 127
字符串类型
char 定长 char(20) tank
varchar 变长 varchar(20) tank
枚举类型
enum
日期类型
datetime
timestamp
mysql> create table student(id int,name varchar(20),age tinyint,gender enum('f','m','qls'),cometime datetime);
mysql> desc student;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('f','m','qls') | YES | | NULL | |
| cometime | datetime | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
##数据约束
1.非空： not null
2.主键： primary key
3.自增： auto_increment
4.无符号： unsigned
5.默认值： default
create table student2(
id int not null primary key auto_increment,
name varchar(20) not null,
age tinyint unsigned not null,
gender enum('f','m'),
cometime datetime default NOW());
create table student4(
id int not null primary key auto_increment comment '学生学号',
name varchar(20) not null comment '学生姓名',
age tinyint unsigned not null comment '学生年龄',
gender enum('f','m') comment '学生性别',
cometime datetime default NOW()) comment '入学时间';
#删
mysql> drop table student3;
#改
1.插入字段
mysql> alter table stu add qiandao varchar(10);
2.在最前面插入字段
mysql> alter table stu add sb varchar(10) first;
3.将字段插入到某个字段的后面
mysql> alter table stu add wpw varchar(10) after qls;
4.删除字段
mysql> alter table stu drop wpw;
5.修改字段属性
mysql> alter table stu modify qls int;
6.修改字段名字，也可以修改属性
mysql> alter table stu change qls myj varchar(5);
7.修改表名
mysql> alter table stu rename stu3;
2.DML
数据操作语言
#插入数据 insert
1.不规范
mysql> insert into student4 values(1,'qiandao',84,'m',NOW());
2.规范写法
mysql> insert into student4(name,age,gender) values('qiandao',84,'m');
3.插入多条数据
mysql> insert into student4(name,age,gender) values('qiandao',84,'m'),('qiudao',73,'f');
4.利用表数据插入表数据
mysql> insert into student select * from student4;
#修改数据 update
1.不规范
mysql> update student set gender='m';
2.规范
mysql> update student set gender='m' where name='qiandao';
3.就算修改整列内容，也要加条件
mysql> update student set age=100 where 1=1;
#删除数据 delete
1.不规范
mysql> delete from test.student;
2.规范
mysql> delete from student4 where id=1;
3.规范
mysql> delete from student4 where 1=1;
使用updata代替delele做伪删除
# 1.添加状态列
mysql> alter table student4 add state enum('0','1') default '1';
# 2.使用update删除数据
mysql> update student4 set state='0' where id=7;
# 3.查询数据
mysql> select * from student4 where state='1';
3.DCL
数据控制语句
#查看用户权限
show grants for root@'%';
#grant
grant all on *.* to root1@'%' identified by '123' with grant option;
grant all privileges on *.* to root@'%' identified by '123' with grant option;
#其他参数（扩展）
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connetions_per_hour：一个用户每小时可连接到服务器的次数
max_user_connetions：允许同时连接数量
#给开发开权限
grant select() on ku.biao to dev1@'%' identified by '123' with max_queries_per_hour 1 max_updates_per_hour 1 max_connections_per_hour 1 max_user_connections 1;
#revoke
mysql> revoke select on *.* from root1@'%';
4.DQL
数据查询语句
select：基础用法
#导入数据
[root@db01 ~]# mysql < world.sql
mysql> show tables from world;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
#使用count()函数
mysql> select count(*) from test.city_1;
#函数:
max()
min()
sum()
avg()
count()
distinct()
#查询所有数据
mysql> select * from world.city;
#where
mysql> select * from world.city where id=1;
#范围查询> 、 < 、<>
mysql> select * from world.city where id<10;
mysql> select * from city where countrycode='CHN' or countrycode='USA';
mysql> select * from city where countrycode in ('CHN','USA');
#联合查询
mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
#limit
mysql> select * from test.city_1 where countrycode in ('CHN','USA') limit 120,60;
limit 10:取前10行
limit 10,20:取10行后面20行
#排序order by
升序
mysql> select * from world.city where countrycode='CHN' order by population;
降序
mysql> select * from world.city where countrycode='CHN' order by population desc;
#分组 group by
#此时此刻，我想吟诗一首
1.遇到统计想函数
2.形容词前groupby
3.函数中央是名词
4.列名select后添加
#统计世界上每个国家的总人口数
sum(population)
group by countrycode
select countrycode,sum(population) from world.city group by countrycode;
#统计中国各个省的人口数量(练习)
sum(population)
group by district
select district,sum(population) from world.city where countrycode='CHN' group by district order by sum(population);
#别名
select district as 省 ,sum(population) as 总人口数 from world.city where countrycode='CHN' group by 省 order by 总人口数;
#统计每个国家的城市数量(练习)
count(name)
group by countrycode
select countrycode,count(district) from world.city group by countrycode;
#统计每个国家的省数量(练习)
mysql> select countrycode,count(distinct(district)) from world.city group by countrycode;
#模糊查询
mysql> select * from world.city where countrycode like '%H%';
mysql> select * from world.city where countrycode like '%H';
mysql> select * from world.city where countrycode like 'H%';
select 高级用法
15396104670598
1.传统连接
世界上，小于100人的城市，在哪个省，属于哪个国家，人口数量是多少？
城市名 省名 国家名 人口数量
city.name city.district country.name city.population
select city.name as 城市名,city.district as 省,country.name as 国家,city.population as 城市人口数量
from city,country
where city.population < 100
and city.countrycode=country.code;
#世界上，小于100人的城市，在哪个省，属于哪个国家，人口数量是多少，说的是什么语言？
城市名 省名 国家名 人口数量 语言
city.name city.district country.name city.population countrylanguage.language
select city.name,city.district,country.name,city.population,countrylanguage.language
from city,country,countrylanguage
where city.population < 100
and city.countrycode=country.code
and country.code=countrylanguage.countrycode;
2.内连接
#世界上，小于100人的城市，在哪个省，属于哪个国家，人口数量是多少？
select city.name,city.district,country.name,city.population
from city join country
on city.countrycode=country.code
where city.population < 100;
#世界上，小于100人的城市，在哪个省，属于哪个国家，人口数量是多少，说的是什么语言？
A join B on 1 join C on 2 join D on 3
select city.name,city.district,country.name,city.population,countrylanguage.language
from city join country
on city.countrycode=country.code
join countrylanguage
on city.countrycode=countrylanguage.countrycode
where city.population < 100;
3.自连接
#世界上小于100人的城市说的什么语言？
城市名，语言，人口数量
city.name,countrylanguage.language,city.population
NATURAL JOIN
select city.name,countrylanguage.language,city.population
from city natural join countrylanguage
where city.population < 100;
#前提条件：两个表中必须有相同的列名字，并且数据一致
4.外连接
外连接分为左外连接和右外连接
左外连接：
mysql> select city.name,city.countrycode,country.name from city left join country on city.countrycode=country.code and city.population<100 limit 10;
+----------------+-------------+------+
| name | countrycode | name |
+----------------+-------------+------+
| Kabul | AFG | NULL |
| Qandahar | AFG | NULL |
| Herat | AFG | NULL |
| Mazar-e-Sharif | AFG | NULL |
| Amsterdam | NLD | NULL |
| Rotterdam | NLD | NULL |
| Haag | NLD | NULL |
| Utrecht | NLD | NULL |
| Eindhoven | NLD | NULL |
| Tilburg | NLD | NULL |
+----------------+-------------+------+
右外连接
mysql> select city.name,city.countrycode,country.name from city right join country on city.countrycode=country.code andd city.population<100 limit 10;
+------+-------------+----------------------+
| name | countrycode | name |
+------+-------------+----------------------+
| NULL | NULL | Aruba |
| NULL | NULL | Afghanistan |
| NULL | NULL | Angola |
| NULL | NULL | Anguilla |
| NULL | NULL | Albania |
| NULL | NULL | Andorra |
| NULL | NULL | Netherlands Antilles |
| NULL | NULL | United Arab Emirates |
| NULL | NULL | Argentina |
| NULL | NULL | Armenia |
+------+-------------+----------------------+
这两种连接会把全部的数据显示出来，但是不符合条件的会用null表示，符合调节的显示出来
where和having的区别
1.where是一条一条从磁盘读取，然后进行判断，符合条件放入内存
having是将所有数据读入内存，然后在内存中判断，不符合删除
2.having判断语句可以引用字段的别名（就是as设置的），where则不能使用
3.having可以使用统计函数，where不能使用。having放在group by后面，where在group by前面。
建议：小表在前，大表在后
用int类型插入怎么让数字显示001？
int（3） zerofill
最小位数是3位 不足3位用0填充
