mysql（五）

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填充