MySQL命令与SQL语句
MySQL命令与SQL语句
一、mysql 管理命令
mysql> status #查看数据库的状态（\s）
mysql> exit quit #退出数据库（\q）
mysql> \c #终止当前的SQL语句
mysql> help #查看帮助信息（\h、？、\?）
mysql> source #库内导入数据（\.）
(mysql< /tmp/fill.sql) #库外导数据
mysql> use #切换数据库（\u）
mysql> tee /tmp/a.log #记录操作日志（\T）
mysql> show slave status\G #将结果展示成key:value的形式
mysql> tee a.log
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> \q
[root@db01 ~]# ll
total 352212
-rw-r--r-- 1 root root 243 Dec 2 00:04 a.log
[root@db01 ~]# cat a.log
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> \q
`tee只针对当前会话 就是你再开一个窗口输入命令 a.log里面是不会记录你新窗口输入的东西的
`如果想全局会话保存日志的话：
vim /etc/my.cnf
[client]
tee=a.log
一定要放在客户端里 不要放在服务端里面了
只要是命令行里跟密码相关的命令 上翻是找不到的 但是tee日志里可以看到 所以危危险险
二、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
三、什么是SQL语句？
机构化查询语言
四、SQL语句的分类
1.DDL（data definition language）数据定义语言
create 创建
drop 删除
alter 修改
truncate 截断
针对库的DDL
#增（create）
'创建库名尽量不要用函数的名 会发生错误 就像shell脚本一样 定义变量尽量不用命令名啥的
mysql> create database db;
mysql> create schema(也是库的意思) db1;
mysql> create database db;
ERROR 1007 (HY000): Can't create database 'db'; database exists
'如果存在了db1库 再次创建会报错 这么写就不会报错 开发写代码会让程序尽量不报错 所以这么写
mysql> create database if not exists db;
Query OK, 1 row affected, 1 warning (0.00 sec)
'这是规则的创建语法 指定字符集与校验规则
mysql> create database db2 charset utf8 collate utf8_general_ci;
'不加这么一串创建也可以 但是需要默认字符集为utf8 在配置文件里加上就可以
vim /etc/my.cnf
[mysqld]
character_set_server=utf8
#删（drop）
mysql> drop database zls1;
#改（alter）也就只能改字符集和校验规则了,改库名只能物理修改mv了
mysql> alter database zls charset gbk;
mysql> show create database db;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| db | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
针对表的DDL
#数据类型
1.整型
int -2^31 ~ 2^31-1
tinyint -128 ~ 127
2.字符串类型
char 定长 char(20) #tank 占20个字节 固定知道的字节就用char
varchar 变长 varchar(20) #tank 占4个字节 不固定不知道的就用varchar
3.枚举类型
enum （给值 只能从指定的里面选）
4.日期类型
datetime（1000-9999）
timestamp （1970.01.01-2038.01.19）
#数据约束
1.非空： not null
2.主键： primary key
3.自增： auto_increment
4.无符号： unsigned
5.默认值： default
6.注释： comment
7.单独的唯一的：unique key
#增
第一次创建
mysql> create table student(id int,name varchar(20),age tinyint,gender enum('f','m'),cometime datetime);
mysql> insert into student values(1,'msy',18,'f',NOW());
#根据上面这个创建的 但是并不规范 没有约束条件
mysql> select * from student;
+------+------+------+--------+---------------------+
| id | name | age | gender | cometime |
+------+------+------+--------+---------------------+
| 1 | msy | 18 | f | 2019-12-02 01:33:44 |
+------+------+------+--------+---------------------+
第二次约束创建
mysql> create table student2(
->id int not null primary key auto_increment,
->name varchar(20) not null,
->age tinyint unsigned not null,
#无符号就包括非空了 非要加not null 就加在后面，
#你不加unsigned无符号时 年龄输入128会报错 加上之后就不会报错 因为-128-127会自动变成0-255
->gender enum('f','m'),
->cometime datetime default NOW());
第三次完美规则创建
mysql> 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.将字段插入到qls字段的后面
mysql> alter table stu add msy varchar(10) after qls;
4.删除字段
mysql> alter table stu drop msy;
5.修改字段属性
mysql> alter table stu modify qls int;
6.修改字段名字，也可以修改属性
mysql> alter table stu change qls msy varchar(5);
7.修改表名
mysql> alter table stu rename stu3;
8.添加主键
mysql> alter table stu add primary key zhujian(sb);
2.DML (Data Manipulation Language) 数据操作语言
insert 增加
delete 删除
update 修改
#插入数据 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;
使用update代替delete做伪删除
# 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 (Data Control Language) 数据控制语言
grant 向用户赋予权限/角色
revoke 撤销用户的权限/角色
#grant
mysql> grant all on *.* to msy@'%' identified by '123' with grant option;
#其他参数（扩展）
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connetions_per_hour：一个用户每小时可连接到服务器的次数
max_user_connetions：允许同时连接数量
#允许同时连接数量为1:
mysql> grant all on *.* to msy@'%' identified by '123' with max_user_connections 1;
#连接了一个
[root@db01 ~]# mysql -umsy -p123 -h10.0.0.51
mysql>
#再连第二个
[root@db01 ~]# mysql -umsy -p123 -h10.0.0.51
Warning: Using a password on the command line interface can be insecure.
ERROR 1226 (42000): User 'msy' has exceeded the 'max_user_connections' resource (current value: 1)
#可以这样连续给参数
mysql> 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;
#当你给查询数量为1时 mysql连接时 就已经算一次了 所以啥也干不成
#revoke
#撤销个查权限
mysql> revoke select on *.* from msy@'%';
#看看还有啥权限
mysql> show Grants for msy@'%'\G;
*************************** 1. row ***************************
Grants for msy@%: GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'msy'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH MAX_USER_CONNECTIONS 1
4.DQL (Data Query Langauge) 数据查询语言
select 查询
#查看建库语句（字符集）
#使用二进制装的就是拉丁字符集
mysql> show create database zls;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| zls | CREATE DATABASE `zls` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
#使用源码安装的是utf8集
mysql> show create database db;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| db | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
#查看表结构
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 | |
+----------+---------------------+------+-----+---------+-------+
#查看自己当前在哪个库
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
#查看数据库的默认的引擎类别
mysql> show variables like '%storage_engine%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine | InnoDB |
+----------------------------+--------+
导入数据小玩一波
# 导入数据
[root@db01 ~]# mysql < world.sql
mysql> show tables from world;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
#想看看这个导入的新表里有点啥
mysql > select * from city;[在生产环境中千万不要用 如果大占内存 会oom]
mysql> select count(*) from world.city;[先这样看看]
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1.函数
max()
min()
sum()
avg()
count()
distinct()
2.查询所有数据
mysql> select * from world.city;
3.where
mysql> select * from world.city where id=1;
+----+-------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
+----+-------+-------------+----------+------------+
#查询甘肃省所有城市信息
SELECT * FROM city WHERE district='gansu';
#查询中国人口数量大于500w
SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;
#查询中国或美国城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
**4.范围查询> 、 < 、<> **
mysql> mysql> select * from world.city where id <10;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
+----+----------------+-------------+---------------+------------+
mysql> select * from world.city where countrycode='CHN';
+------+---------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+----------------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1893 | Tianjin | CHN | Tianjin | 5286800 |
| 1894 | Wuhan | CHN | Hubei | 4344600 |
| 1895 | Harbin | CHN | Heilongjiang | 4289800 |
| 1896 | Shenyang | CHN | Liaoning | 4265200 |
| 1897 | Kanton [Guangzhou] | CHN | Guangdong | 4256300 |
| 1898 | Chengdu | CHN | Sichuan | 3361500 |
| 1899 | Nanking [Nanjing] | CHN | Jiangsu | 2870300 |
| 1900 | Changchun | CHN | Jilin | 2812000 |
| 1901 | Xi´an | CHN | Shaanxi | 2761400 |
| 1902 | Dalian | CHN | Liaoning | 2697000 |
......
#查询世界上人口数量大于100w小于200w的城市信息
SELECT * FROM city WHERE population >1000000 AND population <2000000;
SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;
**5.联合查询 **
mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
mysql> select * from world.city where countrycode in ('CHN','USA');
......
| 2250 | Huaying | CHN | Sichuan | 89400 |
| 2251 | Pingyi | CHN | Shandong | 89373 |
| 2252 | Huangyan | CHN | Zhejiang | 89288 |
| 3793 | New York | USA | New York | 8008278 |
| 3794 | Los Angeles | USA | California | 3694820 |
| 3795 | Chicago | USA | Illinois | 2896016 |
| 3796 | Houston | USA | Texas | 1953631 |
| 3797 | Philadelphia | USA | Pennsylvania | 1517550 |
......
6.limit
#取前十
mysql> mysql> select * from world.city limit 10;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
| 10 | Tilburg | NLD | Noord-Brabant | 193238 |
+----+----------------+-------------+---------------+------------+
#加步长 ~= 翻页
mysql> select * from world.city limit 0,5;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
+----+----------------+-------------+---------------+------------+
mysql> select * from world.city limit 5,5;
+----+-----------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+-----------+-------------+---------------+------------+
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
| 10 | Tilburg | NLD | Noord-Brabant | 193238 |
+----+-----------+-------------+---------------+------------+
mysql> select * from world.city limit 10,5;
+----+-----------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+-----------+-------------+---------------+------------+
| 11 | Groningen | NLD | Groningen | 172701 |
| 12 | Breda | NLD | Noord-Brabant | 160398 |
| 13 | Apeldoorn | NLD | Gelderland | 153491 |
| 14 | Nijmegen | NLD | Gelderland | 152463 |
| 15 | Enschede | NLD | Overijssel | 149544 |
+----+-----------+-------------+---------------+------------+
7.排序order by
#升序
mysql> select * from world.city where countrycode='CHN' order by population;
+------+---------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+----------------+------------+
| 2252 | Huangyan | CHN | Zhejiang | 89288 |
| 2251 | Pingyi | CHN | Shandong | 89373 |
| 2250 | Huaying | CHN | Sichuan | 89400 |
| 2249 | Junan | CHN | Shandong | 90222 |
| 2248 | Shaowu | CHN | Fujian | 90286 |
| 2247 | Xilin Hot | CHN | Inner Mongolia | 90646 |
| 2246 | Linhai | CHN | Zhejiang | 90870 |
| 2245 | Putian | CHN | Fujian | 91030 |
| 2244 | Tumen | CHN | Jilin | 91471 |
.......
#降序
mysql> select * from world.city where countrycode='CHN' order by population desc;
+------+---------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+----------------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1893 | Tianjin | CHN | Tianjin | 5286800 |
| 1894 | Wuhan | CHN | Hubei | 4344600 |
| 1895 | Harbin | CHN | Heilongjiang | 4289800 |
| 1896 | Shenyang | CHN | Liaoning | 4265200 |
| 1897 | Kanton [Guangzhou] | CHN | Guangdong | 4256300 |
| 1898 | Chengdu | CHN | Sichuan | 3361500 |
......
8.分组 group by
1.遇到统计想函数
2.形容词前groupby
3.函数中央是名词
4.列名select后添加
#统计世界上每个国家的总人口数
sum(population)
group by countrycode
select countrycode,sum(population) from world.city group by countrycode;
mysql> select countrycode ,sum(population) from world.city group by countrycode;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| ABW | 29034 |
| AFG | 2332100 |
| AGO | 2561600 |
| AIA | 1556 |
| ALB | 270000 |
| AUS | 11313666 |
......
#统计中国各个省的人口数量
sum(population)
group by district
select district,sum(population) from world.city where countrycode='CHN' group by district order by sum(population);
mysql> select district ,sum(population) from world.city where countrycode='CHN' group by district;
+----------------+-----------------+
| district | sum(population) |
+----------------+-----------------+
| Anhui | 5141136 |
| Chongqing | 6351600 |
| Fujian | 3575650 |
| Gansu | 2462631 |
| Guangdong | 9510263 |
| Guangxi | 2925142 |
| Guizhou | 2512087 |
......
#别名 统计中国各个省的人口数量
select district as 省 ,sum(population) as 总人口数 from world.city where countrycode='CHN' group by 省 order by 总人口数;
mysql> select district as 省,sum(population) as 总人口数 from world.city where countrycode='CHN' group by 省 order by 总人口数;
+----------------+--------------+
| 省 | 总人口数 |
+----------------+--------------+
| Tibet | 120000 |
| Hainan | 557120 |
| Qinghai | 700200 |
| Ningxia | 802362 |
| Yunnan | 2451016 |
| Gansu | 2462631 |
| Guizhou | 2512087 |
......
#倒叙 统计中国各个省的人口数量
mysql> mysql> select countrycode as 国家,count(name) as 城市 from world.city group by 国家 order by 城市 desc;
+--------+--------+
| 国家 | 城市 |
+--------+--------+
| CHN | 363 |
| IND | 341 |
| USA | 274 |
| BRA | 250 |
| JPN | 248 |
| RUS | 189 |
......
#统计每个国家的城市数量
count(name)
group by countrycode
mysql> select countrycode,count(name) from world.city group by countrycode;
mysql> select countrycode,count(name) from world.city group by countrycode;
+-------------+-------------+
| countrycode | count(name) |
+-------------+-------------+
| ABW | 1 |
| AFG | 4 |
| AGO | 5 |
| AIA | 2 |
| ALB | 1 |
| AND | 1 |
| ANT | 1 |
......
#统计每个国家的省数量 distinct是去重的意思
mysql> select countrycode,count(distinct(district)) from world.city where countrycode='CHN' group by countrycode;
+-------------+---------------------------+
| countrycode | count(distinct(district)) |
+-------------+---------------------------+
| CHN | 31 |
+-------------+---------------------------+
9.模糊查询
#不管H在前中后
mysql> mysql> select * from world.city where countrycode like '%H%';
+------+-------------------------+-------------+------------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-------------------------+-------------+------------------------+------------+
| 148 | Nassau | BHS | New Providence | 172000 |
| 149 | al-Manama | BHR | al-Manama | 148000 |
| 201 | Sarajevo | BIH | Federaatio | 360000 |
| 202 | Banja Luka | BIH | Republika Srpska | 143079 |
......
#H在最后
mysql> select * from world.city where countrycode like '%H';
+------+-------------+-------------+------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-------------+-------------+------------------+------------+
| 201 | Sarajevo | BIH | Federaatio | 360000 |
| 202 | Banja Luka | BIH | Republika Srpska | 143079 |
| 203 | Zenica | BIH | Federaatio | 96027 |
| 756 | Addis Abeba | ETH | Addis Abeba | 2495000 |
......
#H在最前
mysql> select * from world.city where countrycode like 'H%';
+------+-------------------------+-------------+------------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-------------------------+-------------+------------------------+------------+
| 936 | Kowloon and New Kowloon | HKG | Kowloon and New Kowl | 1987996 |
| 937 | Victoria | HKG | Hongkong | 1312637 |
| 933 | Tegucigalpa | HND | Distrito Central | 813900 |
| 934 | San Pedro Sula | HND | Cortés | 383900 |
......
10.having
#统计中国每个省的总人口数，只打印总人口数小于100
SELECT district,SUM(Population)
FROM city
WHERE countrycode='chn'
GROUP BY district
HAVING SUM(Population) < 1000000 ;
select 高级用法
# 1. 创建表
mysql> create table stu(id int,name varchar(20));
mysql> create table score(id int,score int);
# 2.插数据
mysql> insert into stu values(1,'qiudao'),(2,'qiaodao'),(3,'haoda');
mysql> insert into score values(1,90),(2,110),(3,120);
# 3.三张表查需要的数据
mysql> select stu.name,score.score from stu,score where stu..name='qiudao' and stu.id=score.id;
+--------+-------+
| name | score |
+--------+-------+
| qiudao | 90 |
+--------+-------+
1.传统连接
#世界上小于100人的城市，在哪个省，属于哪个国家，人口数量是多少？
城市名 省名 国家名 人口数量
city.name city.district country.name city.population
mysql> 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;
+-----------+-----+----------+--------------------+
| 城市名 | 省 | 国家 | 城市人口数量 |
+-----------+-----+----------+--------------------+
| Adamstown | – | Pitcairn | 42 |
+-----------+-----+----------+--------------------+
#世界上小于100人的城市，在哪个省，属于哪个国家，人口数量是多少，说的是什么语言？
城市名 省名 国家名 人口数量 语言
city.name city.district country.name city.population countrylanguage.language
mysql> 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;
mysql> select city.name as 城市名,city.district as 省,country.name as 国家,city.population as 城市人口数量,counuage.language as 语言
-> from city,country,countrylanguage
-> where city.population < 100
-> and city.countrycode=country.code
-> and country.code=countrylanguage.CountryCode;
+-----------+-----+----------+--------------------+-------------+
| 城市名 | 省 | 国家 | 城市人口数量 | 语言 |
+-----------+-----+----------+--------------------+-------------+
| Adamstown | – | Pitcairn | 42 | Pitcairnese |
+-----------+-----+----------+--------------------+-------------+
2.内连接
生产环境这个使用的多
#世界上小于100人的城市，在哪个省，属于哪个国家，人口数量是多少？
select city.name,city.district,country.name,city.population
-> from city join country
-> on city.countrycode=country.code
-> where city.population < 100;
+-----------+----------+----------+------------+
| name | district | name | population |
+-----------+----------+----------+------------+
| Adamstown | – | Pitcairn | 42 |
+-----------+----------+----------+------------+
#世界上小于100人的城市，在哪个省，属于哪个国家，人口数量是多少，说的是什么语言？
mysql> 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;
+-----------+----------+----------+------------+-------------+
| name | district | name | population | language |
+-----------+----------+----------+------------+-------------+
| Adamstown | – | Pitcairn | 42 | Pitcairnese |
+-----------+----------+----------+------------+-------------+
3.自连接
#世界上小于100人的城市说的什么语言？
mysql> select city.name,countrylanguage.language,city.population
-> from city natural join countrylanguage
-> where city.population < 100;
+-----------+-------------+------------+
| name | language | population |
+-----------+-------------+------------+
| Adamstown | Pitcairnese | 42 |
+-----------+-------------+------------+
#前提是两个表中必须有相同的列名字，并且数据一致
4.外连接（左外连接，右外连接）
#左外连接
mysql> select city.name,country.code,country.name
from city left join country
on city.countrycode=country.code
and city.population<100 limit 10;
+----------------+------+------+
| name | code | name |
+----------------+------+------+
| Kabul | NULL | NULL |
| Qandahar | NULL | NULL |
| Herat | NULL | NULL |
| Mazar-e-Sharif | NULL | NULL |
| Amsterdam | NULL | NULL |
| Rotterdam | NULL | NULL |
| Haag | NULL | NULL |
| Utrecht | NULL | NULL |
| Eindhoven | NULL | NULL |
| Tilburg | NULL | NULL |
+----------------+------+------+
#右外连接
mysql> select city.name,country.code,country.name
from city right join country
on city.countrycode=country.code
and city.population<100 limit 10;
+------+------+----------------------+
| name | code | name |
+------+------+----------------------+
| NULL | ABW | Aruba |
| NULL | AFG | Afghanistan |
| NULL | AGO | Angola |
| NULL | AIA | Anguilla |
| NULL | ALB | Albania |
| NULL | AND | Andorra |
| NULL | ANT | Netherlands Antilles |
| NULL | ARE | United Arab Emirates |
| NULL | ARG | Argentina |
| NULL | ARM | Armenia |
+------+------+----------------------+
5.DTL (Data Transaction Language) 数据事务语言
commit 提交事务
rollback 回滚事务
[注意]只有DML语言才有事务性
ACID:指数据库事务正确执行的四个基本要素的缩写。
原子性（Atomicity）:
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）:
事务前后数据的完整性必须保持一致。
隔离性（Isolation）:
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）:
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
浙公网安备 33010602011771号