MySQL-SQL语法基础
一、客户端命令
1.1、mysql命令
-u -p -S -h -P -e mysql -uroot -p -e "show status"
1.2、mysqladmin命令
1.2.1、命令帮助及基础语法
mysqladmin --help mysqladmin -u<name> -p<password> commands
1.2.2、示例
mysqladmin version mysqladmin status mysqladmin ping mysqladmin processlist mysqladmin shutdown mysqladmin variables
1.2、mysqldump命令
见备份与恢复
1.4、接受用户命令
help \G ctrl+c #5.7 以上版本,结束上条命令运行,类似linux ctrl+d \q exit quit use source source /root/world.sql
系统相关命令：
List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. #存储过程及函数 edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement.
二、SQL分类
SQL: 结构化查询语言（Structured Query Language）
DDL : 数据定义语言（Data define Language）
DCL : 数据控制语言（Data Control Language）
DML : 数据操作语言（Data Manipulation Language）
DQL : 数据查询语言（Data QueryLanguage）
三、DDL：数据定义语言
3.1、create database语句
1）当创建的数据库本身存在而且没有写明if not exists子句时，则创建数据库的语句会报错
2）create_specification子句指明创建的数据库的属性，并存储在db.opt文件中
- Character set属性指明此数据库的默认字符集
- Collate属性指明此数据库的默认排序规则
3）可以直接通过mkdir的操作系统命令在数据目录创建文件夹并授权，则MySQL会识别为一个数据库，并在执行show databases命令时可以看到（mysql8.0不再支持）
3.1.1、示例
CREATE DATABSE wordpress CHARSET utf8; #可以指定字符集及排序规则 语法：CREATE DATABASE db_name CHARACTER SET charset_name COLLATE collation_name CREATE DATABASE oldgirl CHARACTER SET gbk COLLATE gbk_chinese_ci; show character set; #找字符集和校对规则. #改库的字符集 ALTER DATABASE [db_name] CHARACTER SET charset_name COLLATE collation_name ALTER DATABASE oldgirl CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; #查看当前所在的库 select database(); #查看当前连接的用户 select user();
3.1.2、开发规范
(1) 库名字必须小写,不能有数字开头,不要超过16个字符 ***** (2) 库名必须和业务有关 (3) 建库时必须添加字符集 (4) 生产中禁止DROP命令 (5) 做变更类操作,需要提前备份
3.2、drop database语句（危险）
3.3、alter database语句
如果修改了数据库的默认字符集或排序规则，那数据库中的所有存储过程和函数都需要重新创建一遍
3.3.1、示例
#例如：更改库的字符集（utf8 ----> utf8mb4） alter database abc charset utf8mb4; alter database abc character set utf8mb4;
3.4、create table语句
建表语法：https://dev.mysql.com/doc/refman/5.7/en/create-table.html
create table students2(sid int,sname varchar(10)); create table test3.students2(sid int,sname varchar(10)); ##在test3这个数据库下创建students2表 create table if not exists students2(sid int,sname varchar(10));
3.4.1、temporary关键词
temporary关键词表示创建的是临时表，临时表仅对本链接可见，另外的数据库链接不可见，当本链接断开时，临时表也自动被drop掉
mysql> create temporary table temp1(sid int,sname varchar(10)); mysql> insert into temp1 values(1,'a'); mysql> select * from temp1; +------+-------+ | sid | sname | +------+-------+ | 1 | a | +------+-------+ 1 row in set (0.00 sec) mysql> \q Bye #本数据库链接断开后再链接，临时表也不存在 [root@db01 ~]# mysql -uroot -pmysql mysql> select * from abc.temp1; ERROR 1146 (42S02): Table 'abc.temp1' doesn't exist #另一个数据库链接执行相同的查询语句查不到数据
3.4.2、常见数据类型
1）数字类
TINYINT #-128~127 ,1-256 INT # -2^31~2^31-1 2^32 正数
2）字符类
char(定长)
varchar(变长)
enum('',''.....) #枚举类型
char和varchar区别？
char(11):定长长度的字符串类型 (1)11 这个数字,表示的是字符显示长度,11是个阈值, (2)存储数据时,会立即分配11个字符串长度的磁盘空间,存不满,用空格填充. 注释: 磁盘空间,utf8 每个字符都是占三个字节长度,utf8mb4,每个字符占4个字节 (3)应用场景 短字符串,固定长度的. varchar(20): 可变长度的字符串类型 (1)20这个数字,表示的是字符显示长度,20是个阈值 (2)存储数据时,比如:zhangsan,SQL引擎会数一下多少个,然后再按照长度分配存储空间,额外开辟1个字节存储字符长度，超过255个字符,字符长度需要2个字节存储 (3) 应用场景 变长长度的字符串存储. #char和varchar选择问题? 短字符串,固定长度的,选择char 变长长度的字符串,选择varchar
enum('',''.....)：枚举类型：
enum('heilongjiang','jilin','liaoning',.....)
1 2 3
(1) 业务当中的数据,限定在某个范围内的值时,值越多性能越好
(2) 不建议存储数字： https://dev.mysql.com/doc/refman/8.0/en/enum.html
(3) enum 的值,相当于一个列表,会自动生成枚举索引.
将来表中存储数据时,只需要存储索引号就可以.减少了磁盘空间的消耗,也减少了聚集索引树的高度
(4)使用enum,尽量是一次性规划好,少去改enum
(5)必须开启SQL_MODE严格模式(5.7以前)
3）时间类型
1、date 2、time 3、year 4、datatime 5、timestamp #datetime与timestamp区别 datetime类型取值范围：1000-01-01 00:00:00 到 9999-12-31 23:59:59 timestamp类型取值范围：1970-01-01 00:00:00 到 2037-12-31 23:59:59（经过测试得出范围为 1970-01-01 08:00:01 到2038-01-19 11:14:07）
4）二进制类
3.4.3、示例
#一个标准的建表语句：
USE abc;
CREATE TABLE stu(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '学生姓名',
sage TINYINT UNSIGNED COMMENT '学生年龄',
gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
telnum CHAR(11) NOT NULL COMMENT '手机号',
intime DATETIME NOT NULL DEFAULT NOW()
)ENGINE INNODB CHARSET utf8;
mysql> desc stu;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | NO | | NULL | |
| sage | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('m','f','n') | NO | | n | |
| telnum | char(11) | NO | | NULL | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | |
+--------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)
#其他建表语句：
create table t1 like student; #创建与student表结构相同的表
insert into t1 select * from student; #将student表中的数据插入到t1中
create table t3 as select * from student; #与 student表结构数据相同，但索引等约束条件不会携带过来Ignore和replace表示在插入数据的过程中如果新表中碰到违反唯一约束的情况下怎么处理，ignore表示不插入，replace表示替换已有的数据，默认两个关键词都不写则碰到违反的情况会报错
3.4.4、表及列属性
1）表属性
ENGINE INNODB #注:在 5.5以前,默认的引擎类型是MyISAM,5.5以后默认是InnoDB CHARSET utf8;
2）列属性
NOT NULL: 非空约束
作用: 此列不能录入空值,我们建议尽量所有列都设置为非空,主要是影响了索引的应用
如果实在不知道填写什么值,可以使用0填充,一般情况会配合default来使用.
create table students3(sid int,sname varchar(10),gender int default 0);
Unique key: 唯一键(索引)约束
作用:此列录入数据时,不能有重复值.
例如:手机号,QQ号
primary key:主键(聚集索引)约束
作用:此列数据录入时,既非空又唯一
一般情况下是id,而且是自动增长的列
使用方法: NOT NULL AUTO_INCREMENT PRIMARY key
开发规范要求: 在建表的时候一定要指定主键
Auto_increment：表示字段为整数或者浮点数类型的value+1递增数值，value为当前表中该字段最大的值，默认是从1开始递增；一个表中只容许有一个自增字段，且该字段必须有key属性，不能含有default属性，且插入负值会被当成很大的正数
Constraint表示为主键、唯一键、外键等约束条件命名，如果没有命名则MySQL会默认给一个
Key/index表示索引字段
Foreign key：表示该字段为外键字段
CREATE TABLE `gender` (
gender_id`int(11) NOT NULL,
name varchar(10) DEFAULT NULL,
PRIMARY KEY (gender_id)
)
create table students5(sid int not null primary key auto_increment,sname varchar(10) unique,gender int,constraint for_1 foreign key (gender) references gender(gender_id));3.4.5、开发规范
(1) 表名不能大写,数字开头,16个字符串 (2) 表名和业务有关 (3) drop 语句禁止 (4) 选择合适的数据类型 (5) 必须要有主键 (6) 列尽量非空约束 (7) 减少外键约束 (8) 必须设置存储引擎和字符集 (9) 列必须要有注释 (10) 对于非负数设置数据类型约束--无符号
3.5、drop table语句（危险）
3.6、alter table语句
3.6.1、示例
1）修改表的字符集
ALTER TABLE stu CHARSET utf8mb4; SHOW CREATE TABLE stu;
2）修改表的存储引擎
ALTER TABLE stu ENGINE MYISAM; ALTER TABLE stu ENGINE INNODB; #扩展:此命令除了可以修改引擎,还可以触发InnoDB表的碎片整理 ALTER TABLE stu ENGINE tokudb;
3）修改表名
ALTER TABLE stu RENAME student; DESC student;
4）添加列和列定义
#添加单列及定义 ALTER TABLE student ADD age INT; #添加多列及定义 ALTER TABLE student ADD gender VARCHAR(20) , ADD qq INT; ----------------------------------------------------------------- #指定位置添加列 #表首列添加一列 ALTER TABLE student ADD classid VARCHAR(20) FIRST; #在age列后面添加一个telnum列 ALTER TABLE student ADD telnum INT AFTER age; #在sname列前面添加一个birthday列（没有before的定义，转换为某列的前面） ALTER TABLE student ADD birthday VARCHAR(30) AFTER sid;
5）删除列及列定义
ALTER TABLE student DROP age; ALTER TABLE student DROP gender;
6）修改列及列定义
#将birthday 数据类型替换为 DATETIME ALTER TABLE student MODIFY birthday DATETIME ; DESC student; #将birthday列名字改为birth ALTER TABLE student CHANGE birthday birth DATETIME; #同时可以更改数据类型
3.7、truncate table语句（危险）
delete from test; #逻辑删除，一行一行删。 truncate table test; #物理删除，pages(block)，效率高。
3.8、create view语句
1）Create view语句是指将某个查询数据的定义保留下来，以便随时调用
2）view本身不存储查询结果，只是一个定义
3）当视图被创建之后，则其定义就已经固定不会再改变，比如一个视图是由select *创建的，则后续对表增加的字段不会成为视图的一部分，而后续对表删除字段则会导致查询视图失败
4）创建的视图默认情况下是属于当前数据库的，当要创建到另外的数据库时则需要在视图名前面加上数据库名
5）视图在满足特定条件时是可以执行insert/update/delete语句的，条件就是视图中的每一行和视图对应的表中的每行数据都能一一对应起来
6）Alter view语句用来修改视图的定义，本身的语法结构和create view相同，语句所起到的作用和create or replace view语句相同
7）Drop view语句用来删除一个或多个视图: drop view v_students_male;
CREATE VIEW test.v AS SELECT * FROM test2.t; CREATE VIEW v_today (today) AS SELECT CURRENT_DATE; ##指定字段名 CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t; create view v_students_male as select sid,sname from students where sex=0; Select * from v_students_male create view v_students_female as select sid,sname from students where sex=1; select * from v_students_female where sid>1; create or replace view v_students_male as select sid,sname,sex from students where sex=0; ##添加or replace参数后，原视图被替换 alter view v_students_male as select sid,sname from students where sex=0;
3.9、create index语句
create index idx_st_sname on students(sname); ##创建普通索引 create index idx_st_union on students(sname,sex); ##创建复合索引 create unique index idx_st_sid on students(sid); ##创建唯一索引 mysql> insert into students values(1,‘eee’,0); ##插入重复数据失败 ERROR 1062 (23000): Duplicate entry '1' for key 'idx_st_sid' #Comment string代表可以为索引添加最长1024的注释 CREATE TABLE t1 (id INT); CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
3.10、rename table语句
1）Rename table语句用来重命名一个或多个表名
2）当想让两个表名相互调换时，可以执行语句
RENAME TABLE old_table TO tmp_table,new_table TO old_table,tmp_table TO new_table;3）Rename table能将表中的数据，索引，主键定义都自动转换到新表下，但视图和对原表分配的权限不能自动转换到新表，需要手动执行
rename table students to students_test;
3.11、DDL小结
3.11.1、库的定义常用命令
create database drop database alter database show databases; show create database
3.11.2、表的定义常用命令
create table drop table alter table show tables show create table desc show table status from abc; #查看库中表的状态
四、DCL : 数据控制语言
常用DCL命令：
grant revoke lock 等
五、DML : 数据操作语言
5.1、insert语句
Insert into students values(1,'aaa'); Insert into students set sid=2,sname='bbb'; Insert into students select * from students_bak; ----------------------------------------------------------------------------------------------------------- #Ignore关键词代表insert语句如果违反主键和唯一键的约束条件，则不报错而只产生警告信息，违反的行被丢弃，而不是整个语句回退；在数据类型转换有问题时如果有ignore则只产生警告信息，而不是语句回退 insert ignore into students values(1,'bbb',0); ----------------------------------------------------------------------------------------------------------- #Insert…select语句详解 用于从另外的表中查出记录并插入到目标表中 INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100; 当目标表和select语句中的表相同时，则会先将select语句的结果存放在临时表中，再插入到目标表中(注意执行顺序) mysql> insert into students select * from students; ----------------------------------------------------------------------------------------------------------- #Insert on duplicate key update语句详解 当insert语句中使用on duplicate keyupdate子句时，如果碰到当前插入的数据违反主键或唯一键的唯一性约束，则Insert会转变成update语句修改对应的已经存在表中的这条数据。比如如果a字段有唯一性约束且已经含有1这条记录，则以下两条语句的执行结果相同 INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE table SET c=c+1 WHERE a=1; On duplicate key update子句后面可以跟多个修改，用逗号隔开 上述例子中如果b字段也有唯一性约束，则与此语句的执行结果相同，但一般应该避免出现对应多条的情况 UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1; mysql> create table students2(sid int primary key,sname varchar(20),sex int); mysql> Insert into students2 values(1,'aaa',1); mysql> Insert into students2 values(1,'bbb',0); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into students2 values(1,'bbb',0) on duplicate key update sname='bbb'; Query OK, 2 rows affected (0.01 sec) mysql> select * from students2; +-----+-------+------+ | sid | sname | sex | +-----+-------+------+ | 1 | bbb | 1 | +-----+-------+------+ 1 row in set (0.00 sec) mysql> insert into students2 values(1,'ccc',0) on duplicate key update sname='ccc',sex=0; Query OK, 2 rows affected (0.01 sec) mysql> select * from students2; +-----+-------+------+ | sid | sname | sex | +-----+-------+------+ | 1 | ccc | 0 | +-----+-------+------+ 1 row in set (0.00 sec)
5.1.1、示例
1）规范的插入语句（所有字段列出，并与值对应）
INSERT INTO student(id ,NAME,sage,gender,telnum,intime,qq) VALUES (1,'zhangsan',18,'m','110',NOW(),'123456');
2）简化写法（不写字段，值按字段顺序录入）
INSERT INTO student VALUES (2,'li4',19,'m','119',NOW(),'123455');
3）有default 或auto_increment,选择性的录入
INSERT INTO student(NAME,sage,telnum,qq) VALUES ('wang5',20,'120','123');4）录入多行数据（生产批量录入建议方法）
如要录入1000w行数据,我们通常会拆成多个工程(事务，拆成1w行作为一个工程),批量录入
INSERT INTO student(NAME,sage,telnum,qq)
VALUES
('ma6',20,'121','1232334'),
('zhao4',22,'231','1212312'),
('qian7',23,'2222','12312344');
5.2、update语句
#单表 update students set sname='abcd',gender='1' where sid=1; Update students set sid=1 where sid=2; ##执行失败，违反唯一性约束 #多表 update students,students2 set students.sname=students2.sname,students.gender=students2.gender where students.sid=students2.sid; ------------------------------------------------------------------------------------------------------ 单表修改是指修改指定单个表中的已经存在数据的一个或多个列的数值；set短语后面跟要修改的列和值； where子句表示限定要修改表中的哪些行数据，如果没有where子句则表示所有行都要修改；order by子句表示update数据按照指定的顺序进行；limit子句表示限定修改数据的行数 多表修改是指修改table_references指定的多个表中满足条件的行数据，多表修改不允许使用order by和limit子句 Ignore关键词表示当修改语句碰到违反唯一性约束条件等情况是，语句不会报错回退而是报警告信息 Update ignore students set sid=1 where sid=2; ##执行成功但数据不会修改 ------------------------------------------------------------------------------------------------------ #以下语句的col1只会比原值增加1 UPDATE t1 SET col1 = col1 + 1; #Order by指定update数据的顺序，在某些情况下可以避免错误的发生，比如t表中的id字段是有唯一约束的，则以下第一个语句执行会报错，而第二个语句执行则正常 UPDATE t SET id = id + 1; UPDATE t SET id = id + 1 ORDER BY id DESC; mysql> update students2 set sid=sid+1; ##执行报错 ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY‘ mysql> update students2 set sid=sid+1 order by sid desc; ##执行成功 Query OK, 2 rows affected (0.04 sec) Rows matched: 2 Changed: 2 Warnings: 0 #多表修改举例（表之间通过where条件进行join操作） UPDATE items,month SET items.price=month.price WHERE items.id=month.id;
切记: UPDATE 语句在SQL92标准中是必须加where，否则全表修改
5.2.1、示例
1）更改字段的值
UPDATE student SET gender='f' WHERE id=3;
5.3、delete语句
#delete单表删除举例 Delete from students; delete from students where sid=1; Delete from students order by sid; Delete from students limit 1; ##只删除先找到的一行 #Delete语句中的order by子句决定了删除数据的顺序，配合limit子句后在某些情况下也非常有用，比如删除最老的一条记录： DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1; #多表删除是指从一个或多个表中删除满足条件的数据，其中的table_references代表了多个表的join操作，例如以下两个例子代表从t1和t2表中删除满足条件的数据 DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id; DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id; #对多表删除语句而言，如果想对表使用别名，则只能在table_references子句中使用，否则会报错 DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id; ##正确 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id; ##正确 DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2 WHERE a1.id=a2.id; ##错误 DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2 WHERE a1.id=a2.id; ##错误
切记:DELETE 语句在SQL92标准中是必须加where，否则全表修改
5.3.1、示例
1）删除行记录
DELETE FROM student WHERE id=6;
5.3.2、屏蔽delete操作
使用伪删除，将delete转换为update
#以学生表为例： 1）添加状态列（1存在，0删除） alter table student add state int not null default 1; 2）使用update更新state为0，标识某行被删除 update student set state=0 where id=6; 3）让应用查询不到伪删除的行 select * from student where state=1;
5.3.3、生产删除数据
生产中如何删除数据： 如要执行：delete from students where sid=3; 1.开启事务，数据删除错误可以回滚 set autocommit=off; rollback; #删除错误回滚 commit; #删除正确提交 2.将要删除的数据select出来，确保删除正确 select * from students where sid =3; 3.执行删除动作 delete from students where sid=3;
六、DQL : 数据查询语言
6.1、select语句
#Order by和group by子句可以引用select_expr中的列，通过以下三种方式： SELECT college, region, seed FROM tournament ORDER BY region, seed; SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s; SELECT college, region, seed FROM tournament ORDER BY 2, 3; Order by子句表示查询结果按照顺序排列，默认是升序排列，可以指定DESC表明按照降序排列 #Having子句一般是跟在group by子句之后，代表限制分组之后的结果 SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary) > 10; #Limit子句用来限制查询结果的条数，其后可以带两位>0的整数，第一位代表offset，第二位代表取多少行 SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows，等同于select * from tbl limit 0,5 SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15 #Select … into语句代表将查询结果写入文件中或者定义的参数变量中 #For update关键词代表将查询的数据行加上写锁，直到本事务提交为止 #Lock in share mode关键词代表将查询的数据行加上读锁，则其他的链接可以读相同的数据但无法修改加锁的数据 #ALL/Distinct关键词代表是否将查询结果中完全重复的行都查询出来，ALL是默认值代表都查询出来，指定distinct代表重复行只显示一次 select count(*),count(all sid),count(distinct sid) from scores; #HIGH_PRIORITY代表赋予读操作较高的操作优先级 #Max_statement_time=N子句代表设置语句执行超时时间（毫秒） #Straight_join关键词代表强制优化器在表连接操作时按照语句中from子句中的表的顺序执行 Sql_big_result/sql_small_result通常是和group by/distinct一起使用，其作用是事先告诉优化器查询结果是大还是小，以便优化器事先准备好将查询结果存放在磁盘临时表或者快速临时表中以便后续操作 #Sql_buffer_result强制将查询结果存入临时表中 #Sql_calc_found_rows关键词代表要求查询结果的同时计算结果的行数，以便后续通过SELECT FOUND_ROWS()直接获取行数 #Sql_cache/sql_no_cache代表是否直接从query cache中获取查询结果 #Select…into语句 Select … into语句代表将查询结果存入定义的变量或者文件 SELECT ... INTO var_list将查询结果存入定义的变量 SELECT ... INTO OUTFILE将查询结果按照一定的格式写入到文件中 SELECT ... INTO DUMPFILE将查询结果以一行的格式写入到文件中，且只能写入一行 当使用存入变量方法是，需要保证查询结果返回一行，如果不返回数据则报no data错误，如果返回多行则报Result consisted of more than one row错误，当返回行数不确定时，可以用limit 1强制只返回一行 SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1; 使用Select … into outfile'file_name'时，文件会创建在本地服务器上，所以要确保你的用户能创建文件，而且此file_name不能已经存在在服务器上以免覆盖其他文件 SELECT sid,sname,sex INTO OUTFILE '/tmp/students.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM students; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement My.cnf配置文件中添加secure_file_priv=/tmp/后重启再执行，成功 mysql> select * into dumpfile '/tmp/students3.txt' from students; mysql> select * into dumpfile '/tmp/students4.txt' from students limit 1;
6.1.1、select简单查询
1）查看表中的所有数据(生产不要出现这条语句)
SELECT * FROM world.city;
2）查询表中某几个字段
SELECT NAME,countrycode FROM world.city;
6.2、where语句
6.2.1、where + 等值查询（=）
SELECT NAME FROM world.city WHERE countrycode='CHN';
6.2.2、where + 不等值查询（> < >= <= <>）
SELECT * FROM world.city WHERE population < 100;
注意事项：
1.除了 <> 或 != ,都属于范围查询,主键除外. 2.对于辅助(普通)索引列查询,避免使用<> 或 !=
6.2.3、WHERE + 逻辑连接符 ( AND , OR)
1）查询中国人口数大于500w的城市信息
SELECT id, NAME ,countrycode,population FROM world.city WHERE countrycode='CHN' AND population > 5000000;2）查询中国吉林省的城市信息
SELECT * FROM world.city WHERE countrycode='CHN' AND district='jilin';3）查询中国和美国的城市信息
SELECT * FROM world.`city` WHERE countrycode ='CHN' OR countrycode='USA';
6.2.4、where + in
1）查询中国和美国的城市信息
SELECT * FROM world.`city` WHERE countrycode IN ('CHN' ,'USA');
#说明:OR和IN属于范围查询,我们一般替换为 UNION 或者 UNION ALL
6.2.5、WHERE + BETWEEN AND
1）查询城市人口500w-600w的城市信息
SELECT * FROM world.city WHERE population>5000000 AND population<6000000; SELECT * FROM world.city WHERE population BETWEEN 5000000 AND 6000000;
6.2.6、WHERE + LIKE 模糊查询
1）查询国家代号为CH打头的城市信息
SELECT * FROM world.city WHERE countrycode LIKE 'CH%'; #注意: LIKE '%aa%' 不要出现 #搜索框类的应用,就是典型的%aa%,建议不要使用MySQL,使用ES
6.3、group by语句（配合聚合函数）
6.3.1、常见聚合函数
SUM() #统计总和 AVG() #统计平均值 MAX() #统计最大值 MIN() #统计最小值 COUNT() #统计数量
6.3.2、示例
1）统计世界上每个国家的总人口数.
SELECT countrycode,SUM(population) FROM world.`city` GROUP BY countrycode ;
2）统计中国各个省的总人口数量
SELECT countrycode, district, SUM(population) FROM world.city WHERE countrycode='CHN' GROUP BY district;
3）统计世界上每个国家的城市数量
SELECT countrycode, COUNT(id) FROM world.city GROUP BY countrycode ;
4）统计中国各个省的平均人口数,显示结果是大于平均人口数的城市信息
SELECT a.name AS '城市名', a.population AS ap, a.countrycode AS '国家' , a.district AS '省' , AVG(a.population) AS '平均人口' FROM world.city AS a WHERE a.countrycode='CHN' GROUP BY a.district HAVING ap > '平均人口'; #对group by后的结果再次进行筛选
注意事项：以上语句在sqlyog里面是可以执行成功的，但是在mysql命令行执行出错：
[world]>SELECT a.name, a.district, a.countrycode, a.population , AVG(a.population) AS avg_po FROM world.city AS a WHERE a.countrycode='CHN' GROUP BY a.district HAVING a.population > avg_po; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.a.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by #在严格模式下: 1. 在 select ,having,order by 中涉及到的列条件必须在group by存在 2. 如果不存在,可以用any_value(a.population) 3. 或者是设置sql_mode去掉only_full_group_by
可以使用如下的语句：
方法一： SELECT any_value(a.population) AS avp, any_value(a.countrycode) AS avc, a.district AS ad , AVG(a.population) AS avg_p FROM world.city AS a WHERE a.countrycode='CHN' GROUP BY ad HAVING avp > avg_p; 方法二： set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
6.4、 limit子句（常用来分页）
LIMIT 10,5 #从第10行开始，取5行 LIMIT 5 OFFSET 10
6.4.1、示例
1）查询中国人口数量排名前十的城市信息
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC LIMIT 10
6.5、多表连接查询
#Select语句中的表连接 在MySQL中，join/inner join/cross join三者的意思是一样的 #Join语句中表别名的用法 SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name; #From子句后面还可以跟子查询，但子查询必须带别名 SELECT * FROM (SELECT 1, 2, 3) AS t1; #当inner join或者表之间用逗号隔开，且没有表之间的关联字段，则代表结果是两者的笛卡尔积 #STRAIGHT_JOIN和Join的用法大致相同，唯一不同是确保左表是先被读取的，以保证优化器的读取顺序 Select语句中的表连接 SELECT * FROM students, scores; ##没有关联条件，表示笛卡尔积 SELECT * FROM students INNER JOIN scores ON students.sid=scores.sid; SELECT * FROM students LEFT JOIN scores ON students.sid=scores.sid; SELECT * FROM students LEFT JOIN scores USING (sid); SELECT * FROM students LEFT JOIN students2 ON students.sid=students2.sid LEFT JOIN scores ON students2.sid=scores.sid; #如果相对union语句的最后结果做排序或者limit限制，则需要将每个select语句用括号括起来，把order by或limit语句放在最后 (Select sid,sname from students) Union (Select sid,sname from students2) order by sid limit 2;
6.5.1、使用及条件
#作用： 要查询的数据,是来自于多张表时,可以用多表连接 #多表连接公式： 1）数据来自多张表，优先想到多表连接join ON 2）关联表写join两端 3）on条件写两表的关联列 4）所有查询条件select后，注意表名和别名 5）where过滤条件写最后 #多表连接查询的必要条件： 多表之间有直接或间接的关联条件
6.5.2、示例
1）查询一下世界上人口小于100人的城市,所在的国家信息(国家名,国土面积,所在大洲)
方法一: 传统 where 连接方法 SELECT city.NAME,city.countrycode,city.population , country.NAME , country.continent, country.SurfaceArea FROM city,country WHERE city.Population<100 AND city.CountryCode=country.code; 方法二: join on 的连接方法 SELECT city.NAME,city.countrycode,city.population , country.NAME , country.continent, country.SurfaceArea FROM city JOIN country ON city.CountryCode=country.code WHERE city.Population<100;
2）统计每个学生的所学课程总成绩
SELECT student.sname , SUM(sc.score) FROM student JOIN sc ON student.sno=sc.sno GROUP BY sc.sno;
3）统计zhang3,学习了几门课
select student.sname, count(sc.cno) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where student.sname='zhang3' group by sc.sno;
4）查询zhang3,学习的课程名称有哪些?
SELECT student.sname,course.cname FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno WHERE student.sname='zhang3';
5）查询oldguo老师教的学生名
SELECT teacher.tname,student.sname FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE teacher.tname='oldguo';
6）统计oldguo所教课程的平均分数
SELECT teacher.tname,course.cname,AVG(sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno WHERE teacher.tname='oldguo';
6.6、子查询
查询里嵌入查询，先执行子查询,再执行外部查询
6.6.1、示例
1）查询一下世界上人口小于100人的城市,所在的国家信息(国家名,国土面积,所在大洲)
SELECT NAME , continent, SurfaceArea FROM country WHERE CODE IN (SELECT countrycode FROM city WHERE population<100);
2）学过python和linux两门课程的学号和姓名
方法一;
SELECT course.cname,student.sno,student.sname
FROM course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE course.cname IN ('linux','python')
方法二：
SELECT course.cname,student.sno,student.sname ,COUNT(course.cname) AS cc
FROM course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE course.cname IN ('linux','python')
GROUP BY student.sno
HAVING cc > 1;
方法三：
SELECT t.sn ,COUNT(t.cc) AS ab FROM
(SELECT course.cname AS cc,student.sno AS ss ,student.sname AS sn
FROM course
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE course.cname IN ('linux','python'))t
GROUP BY t.sn
HAVING ab > 1;6.7、 union [all]语句
将两个结果集并成一个结果接,连接两条select语句结果.union会将查询的结果去重，而union all不会
6.7.1、示例
1）查询中国和美国的城市信息
方法一：
SELECT * FROM city WHERE countrycode IN ('CHN','USA');
方法二：效率更高
SELECT * FROM city WHERE countrycode = 'CHN'
UNION ALL
SELECT * FROM city WHERE countrycode = 'USA';七、元数据获取
7.1、information_schema库
7.1.1、介绍
1.虚拟库
2.开启数据库时产生的
3.不能被删除和修改
4.库中存储的是视图
5.此库中的视图也是不能被删除和修改的,智能select查询
7.1.2、作用
此库中的视图是用来,间接的查询数据库的"元数据"("基表"数据)
基表:数据是通过自动统计收集而来的.
基表:是不允许人为直接增\删\改\查的
必须通过:专用的DDL,DCL实现增\删\改,show语句和information_schema可以做查询
7.2、information_schema.tables介绍
information_schema.tables记录了整个MySQL数据库中,所有的表的详细属性信息
常用字段信息：
TABLE_SCHEMA #表所在的库 TABLE_NAME #表名字 ENGINE #表的存储引擎 TABLE_ROWS #表的行数 AVG_ROW_LENGTH #表的平均行长度(字节) INDEX_LENGTH #表的索引长度(字节)
information_schema.tables全表信息：
mysql> desc information_schema.tables; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) unsigned | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(2048) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+ 21 rows in set (0.00 sec)
7.3、information_schema.tables应用
1）统计各个库下表的个数
mysql> SELECT table_schema,COUNT(table_name) FROM information_schema.tables GROUP BY table_schema; +--------------------+-------------------+ | table_schema | COUNT(table_name) | +--------------------+-------------------+ | abc | 2 | | information_schema | 61 | | mysql | 31 | | performance_schema | 87 | | sys | 101 | | world | 3 | +--------------------+-------------------+ 6 rows in set (0.01 sec)
2）统计各个引擎表的个数
mysql> SELECT ENGINE,COUNT(table_name) FROM information_schema.tables GROUP BY ENGINE; +--------------------+-------------------+ | ENGINE | COUNT(table_name) | +--------------------+-------------------+ | NULL | 100 | | CSV | 2 | | InnoDB | 35 | | MEMORY | 51 | | MyISAM | 10 | | PERFORMANCE_SCHEMA | 87 | +--------------------+-------------------+ 6 rows in set (0.00 sec)
3）列出所有innodb引擎的表
mysql> SELECT table_schema,table_name ,ENGINE FROM information_schema.tables WHERE ENGINE='innodb';
4）world的数据库下每个表的行数（可以分析出库中最大的表）
mysql> SELECT table_name ,table_rows FROM information_schema.tables WHERE table_schema='world'; +-----------------+------------+ | table_name | table_rows | +-----------------+------------+ | city | 4188 | | country | 239 | | countrylanguage | 984 | +-----------------+------------+ 3 rows in set (0.00 sec)
5）统计每个库占用的总空间大小
一张表占用空间大小计算公式：平均行长度*表的行数+索引长度
AVG_ROW_LENGTH * TABLE_ROWS+ INDEX_LENGTH
mysql> SELECT table_schema,SUM(AVG_ROW_LENGTH * TABLE_ROWS+ INDEX_LENGTH)/1024 AS Total_KB FROM information_schema.tables GROUP BY table_schema; +--------------------+-----------+ | table_schema | Total_KB | +--------------------+-----------+ | abc | 0.0000 | | information_schema | NULL | | mysql | 2322.6455 | | performance_schema | 0.0000 | | sys | 15.9961 | | world | 779.7744 | +--------------------+-----------+ 6 rows in set (0.02 sec)
6）information_schema.tables结合concat()函数
vim /etc/my.cnf
secure-file-priv=/tmp
SELECT CONCAT("alter table ",table_name," discard tablespace;")
FROM information_schema.tables WHERE table_schema='world' INTO OUTFILE '/tmp/discard.sql';
[root@db01 tmp]# cat discard.sql
alter table city discard tablespace;
alter table country discard tablespace;
alter table countrylanguage discard tablespace;
7）全库所有的表都单独备份（分表备份）
mysqldump -uroot -p123 world city > /bak/world_city.sql
方法一：使用SQL结合concat函数
select concat("mysqldump -uroot -pmysql ",table_schema," ",table_name," > /bak/",table_schema,"_",table_name,".sql")
from information_schema.tables where table_schema not in ('information_schema','performance_schema','sys') INTO OUTFILE '/tmp/bak.sh';
执行shell脚本
方法二：使用shell脚本
#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin
MYUSER=root
MYSQL_PWD=mysql
MYCMD="mysql --user=$MYUSER"
MYDUMP="mysqldump --user=$MYUSER"
BACKDIR=/server/backup
[ -d $BACKDIR ] || mkdir -p $BACKDIR
for dbname in `$MYCMD -e "show databases;"|grep -vE 'Database|information_schema| performance_schema|sys' `
do
mkdir $BACKDIR/${dbname}_$(date +%F) -p
for table in `$MYCMD -e "show tables from $dbname;"|sed '1d'`
do
$MYDUMP $dbname $table|gzip >$BACKDIR/${dbname}_$(date +%F)/${dbname}_${table}.sql.gz
done
done
八、show 语句
show databases; show tables; show create database xxx show create table show grants for show charset; show collation; show engines; show [global] status ; #看数据库当前状态 show variables like '%log%'; #看数据库参数信息 show [full] processlist; #看数据库连接线程状态 show engine innodb status \G #看innodb存储引擎总状态 show master status #主从复制中主库信息 show slave status \G #主从复制从库状态的 show binary logs #可用的binlog文件名 show binlog events in '' #binlog日志的事件信息 select @@log_error; #查看某个参数的定义信息 select now(); #查看当前时间 select database(); #查看当前use到的库 select user(); #当前登录的用户
作者：Lawrence
-------------------------------------------
个性签名：独学而无友，则孤陋而寡闻。做一个灵魂有趣的人！
扫描上面二维码关注我
如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！
本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
