MySQL（三）

mysql接口程序使用及SQL入门

mysql接口程序：

mysql -uroot -poldboy123 -e "show variables like '%server_id%'"

mysql>

mysql：

用于数据库连接管理
将用户SQL 语句发送到服务器

mysqladmin：
命令行管理工具

mysqldump：
备份数据库和表的内容

1、接口自带的功能

1、h 或 help 或 ?
2、G    以键值对的方式显示结果
3、T 或 tee    记录命令行的输入输出 用法   tee  /var/log/test.log 统计到指定的文件里
4、c 或 CTRL+c     不执行命令
5、s 或 status
6、. 或 source         
　　执行外部SQL脚本：二进制日志截取、备份出来的SQL脚本
7、u 或use  进入某个数据库

2、服务器端命令（SQL）

（1）SQL：结构化的查询语言，mysql接口程序只负责接收SQL，传送给SQL层

（2）SQL种类：

　　DDL:数据库（对象）定义语言
　　DCL:数据库控制语言（grant revoke）
　　DML：数据（行）操作语言（update delete insert）
　　DQL: 数据查询语言（show、select）

DDL操作：

对象： 库和库：

定义什么？

1、库名字
2、库的基本属性
如何定义？
create database lufei;   创建一个lufei库
create schema lf;　　   创建一个lf库 和datbase一样
show databases;
create database llf CHARACTER SET utf8 ; 创建一个库并添加字符集属性为utf8
show create database llf；   查看建库的语句  了解到建库的属性
drop database llf;                   删除库
help create database;            help查询帮助
　　　字符集： CHARACTER SET [=] charset_name
　　　排序规则：COLLATE [=] collation_name

改库的字符集:
ALTER DATABASE [db_name] CHARACTER SET charset_name COLLATE collation_name
mysql> alter database lf charset utf8mb4;
mysql> show create database lf;


表：
表数据：数据行
表属性（元数据）：表名、列名字、列定义（数据类型、约束、特殊列属性）、表的索引信息
定义什么？
定义表的属性。

use lufei; 切换到lufei这个库里
创建：
create table t1 (id int ,name varchar(20));
查询：
show tables;                查看库里的表

show create table t1;   看t1 表的创建命令

desc  t1;                       看列的名字属性

删除
drop table t1;
修改：
（1）在表中添加一列
　　alter table t1 add age int;
（2）添加多列
　　alter table t1 add bridate datetime, add gender enum('M','F');
（3）在指定列后添加一列
　　alter table t1 add stu_id int after id;
（4）在表中最前添加一列
　　alter table t1 add sid int first;
（5）删除列
　　alter table t1 drop sid;
（6）修改列名
　　alter table t1 change name stu_name varchar(20);
（7）修改列属性
　　alter table t1 modify stu_id varchar(20);
（8）修改表名
　　rename table t1 to student;
　　alter table student rename to stu;
-------------------------------------------------------------

DML语句：数据操作语言

insert 

use lufei
create table t1 (id int ,name varchar(20)); 创建一个简单的表来测试

insert into t1 values(1,'zhang3');   　　　　　　　 插入数据
select * from t1;                                                       查看
insert into t1 values (2,'li4'),(3,'wang5'),(4,'ma6');   插入多个数据
insert into t1(name) values ('xyz');                          只插入name列的数据 id列默认为空

select * from  t1;

update

update t1 set name='zhang33' ; 　　　　  　　 ----会更新表中所有行的name字段，比较危险。
update t1 set name='zhang55' where id=1; 　　----update在使用时一般都会有where条件去限制。


delete

delete from t1 ; 　　　　　　--删除表中所有行，比较危险。一行一行删除表中数据。
delete from t1 where id=2;

DDL

truncate table t1; 　　　　　 ---在物理上删除表数据，速度比较快。

--------------------------------------------------------------------------------------

DQL:

　　select语句：
SELECT USER,PASSWORD ,HOST FROM mysql.user;

-- select 基本查询 

关键词 select  。。。。。from

DESC world.city
SELECT id ,NAME FROM world.city;    查询world库里city表的 id和name
SELECT * FROM world.`city`;                  * 查询所有

-- select 条件查询 where

WHERE条件又叫做过滤条件，它从FROM子句的中间结果中去掉所有条件conditions不为TRUE（而为FALSE或者NULL）的行
WHERE子句跟在FROM子句后面
不能在WHERE子句中使用列别名

----1、查询中国（CHN）所有的城市信息 

 SELECT * FROM world.`city` WHERE countrycode='CHN';

---- 2、查询中国（CHN）安徽省所有的城市信息。

SELECT * FROM world.`city` 
WHERE countrycode='CHN'
AND
district='anhui';

---- 3、查询世界上人口数量在10w-20w城市信息

SELECT * FROM world.`city` 
WHERE 
population BETWEEN 100000 AND 200000 ;

---- 4、中国或者日本的所有城市信息    IN  或者的意思

where字句中的IN
SELECT * FROM world.city
WHERE countrycode IN ('CHN','JPN');

---- 5、模糊查询

SELECT * FROM world.city
WHERE countrycode LIKE 'ch%';

 

-- select 排序并限制---- 按照人口数量排序输出中国的城市信息（ASCDESC）

SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population ASC;
SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population DESC;

---- 按照多列排序人口+省排序

SELECT * FROM world.`city` WHERE countrycode='CHN' 
ORDER BY id DESC ;    按列名进行排序

SELECT * FROM city
ORDER BY 5 DESC ;       按列号

1-10
SELECT * FROM world.`city` WHERE countrycode='CHN' 
ORDER BY 5 DESC LIMIT 20;    LIMT 排序一部分   一定要配合order by 使用

11-20
SELECT * FROM world.`city` WHERE countrycode='CHN' 
ORDER BY 5 DESC LIMIT 10,10 ;

SELECT * FROM world.`city` WHERE countrycode='CHN' 
ORDER BY 5 DESC LIMIT 10 OFFSET 10 ;

--表连接查询
-- 表连接查询

DESC city

DESC countrylanguage

传统的连接写法（使用where）

---- 中国所有城市信息+使用语言

SELECT NAME ,countrycode ,population FROM city WHERE countrycode ='CHN'

SELECT countrycode ,LANGUAGE FROM countrylanguage;

SELECT ci.NAME ,ci.countrycode ,ci.population,cl.language 
FROM 
city AS ci , countrylanguage AS cl    设置别名 简化表的名字
WHERE ci.countrycode ='CHN' 
AND
ci.CountryCode=cl.CountryCode;

SELECT NAME,ci.countrycode ,cl.language ,ci.population
FROM city ci , countrylanguage cl
WHERE 
ci.countrycode='chn' AND
ci.`CountryCode`=cl.countrycode;

SELECT NAME,countrycode ,LANGUAGE ,population
FROM city NATURAL JOIN countrylanguage 
WHERE population > 10000000
ORDER BY population;

SELECT NAME,countrycode ,LANGUAGE ,population
FROM city JOIN countrylanguage 
USING(countrycode);

---- 查询青岛这个城市，所在的国家具体叫什么名字

DESC city
DESC country

SELECT NAME,countrycode FROM city WHERE NAME='qingdao';

SELECT NAME FROM country WHERE CODE='CHN';

-------------------------------- 常用方式
SELECT ci.name ,ci.countrycode,ci.population ,co.name
FROM city AS ci 
JOIN 
country AS co
ON ci.countrycode=co.code
AND
ci.name='qingdao';
---------------------------------

group by +聚合函数（avg()、max()、min()、sum()）

统计国家人数
SELECT countrycode ,SUM(population) FROM city
WHERE countrycode = 'chn'  不加where条件则查看所有国家的人口总和
GROUP BY countrycode;

union

用来替换 or 、in()

SELECT * FROM world.city
WHERE countrycode IN ('CHN','JPN');
改写为：

SELECT * FROM world.city
WHERE countrycode ='CHN'
union
SELECT * FROM world.city
WHERE countrycode ='JPN';

------------------

字符集：

charset:字符集
UTF8
UTF8mb4

gbk

collation：排序规则

a-z ,A-Z 大小写敏感

aA-zZ 小写不敏感

show charset;    查看字符集
show collation;   查看校对规则

数据库：

服务器端字符集： 

控制的是，存到mysql中时，字符集控制

客户端字符集

控制的是用户的输入及显示

系统字符集

控制的是系统相关的显示，和一些依赖于操作系统的应用

alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;
alter table t1 CHARACTER SET latin1;

注意：更改字符集时，一定要保证由小往大改，后者必须是前者的严格超集。

生产中别随便改。

数据类型及列属性：

数字类型
字符类型
时间类型

 

 

列属性

create table student(id int not null primary key AUTO_INCREMENT)；
create table student1(id int not null primary key AUTO_INCREMENT,name varchar(20))charset utf8;
create table teacher(id int not null ,name varchar(20) not null);
create table teacher1(id int not null ,name varchar(20) not null,beizhu varchar(20) not null default "ok");

primary key 主键：非空、唯一
unique：唯一

 

获取元数据：

information_schema

元数据

SELECT TABLE_NAME, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'world';

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'set';

 
SELECT CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLLATIONS
WHERE IS_DEFAULT = 'Yes';

SELECT TABLE_SCHEMA, COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA;

 

mysqldump -uroot -poldboy123 world country >> /bakcup/world_country.bak.sql

select table_schema ,country from tables where table_schema='world';

------------------------

concat 拼接

select concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," 
>>","/backup/",table_schema,"_",table_name,".bak.sql") 
from information_schema.tables where table_schema='world';

拼接语句 备份world下的所有表

SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.',
TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.',
TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘world’;

 

 

-------------------------

show
show
show databases
show create database oldboy
show tables
show create table t1

SOHW databases：列出所有数据库
SHOW TABLES：列出默认数据库中的表
SHOW TABLES FROM <database_name>：列出指定数据库中的表 mysql> show tables from world;
SHOW COLUMNS FROM <table_name>：显示表的列结构
SHOW INDEX FROM <table_name>：显示表中有关索引和索引列的信息

　　mysql> show index from world.city;
SHOW CHARACTER SET：显示可用的字符集及其默认整理
SHOW COLLATION：显示每个字符集的整理
SHOW STATUS：列出当前数据库状态
SHOW VARIABLES：列出数据库中的参数定义值