MySQL常用SQL语句
1、对库的操作
mysql> show databases; #查看所有的库 mysql> create database db1; #创建数据库 mysql> use db1; #切换到db1库 mysql> select database(); #查看当前所在的库 mysql> drop database db1; #删除db1库 # 创建和删除带(-)的数据库名，需要使用反引号 mysql> create database `db-1`; mysql> drop database db-1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1 mysql> drop database `db-1`;
2、对表的操作
1 mysql> drop tables test; #删除test表 2 mysql> alter table employee rename emp; #修改表名 3 mysql> desc emp; #查看表结构 4 mysql> show create table emp\G; #查看表详细结构，可加\G 5 mysql> truncate emp; #清空表 6 mysql> delete from employee where id=18; #删除表中某一条记录用delete
3、查询语句
select distinct[去重] max(字段)|min(字段)|avg(字段)|count(字段)|sum(字段) as[起别名] from where[过滤] .... group by[分组] .... having[过滤分组结果] ...... order by[排序] ...... limit[显示内容数] ......
关键字的执行优先级：
from→where→group by→having→select→distinct→order by→limit
4、基本查询
1、避免重复DISTINCT SELECT DISTINCT post FROM emp; 2、通过四则运算查询 SELECT name, salary*12 FROM emp; SELECT name, salary*12 AS Annual_salary FROM emp; 3、定义显示格式 CONCAT() #函数用于连接字符串 SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM emp; CONCAT_WS() #自定义分隔符(第一个参数为分隔符) SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM emp; +----------------------+ | Annual_salary | +----------------------+ | 成龙:120000.00 | | 张野:120001.56 | | 程咬金:240000.00 | | 程咬银:228000.00 | | 程咬铜:216000.00 | | 程咬铁:204000.00 | +----------------------+ 18 rows in set (0.03 sec)
5、过滤条件（单表查询）
where 过滤 1、SQL语句支持的符号(>,<,>=,<=,=,!=) mysql> select name,age,salary,post from emp where post = 'teacher'; mysql> select name,age,salary,post from emp where post != 'teacher'; mysql> select name,age,salary,post from emp where age > 38; mysql> select name,age,salary,post from emp where age < 38; mysql> select name,age,salary,post from emp where age >=38; mysql> select name,age,salary,post from emp where age <=38; 2、in集合查询,between ... and ...匹配范围 mysql> select name,age,salary from emp where salary in (10000,9000,30000); mysql> select name,age,salary from emp where salary not in (10000,9000,30000); mysql> select name,age,salary from emp where salary between 3000 and 10000; mysql> select name,age,salary from emp where salary not between 3000 and 10000; 注意： between 数字1 and 数字2，数字2必需大于数字1 3、模糊匹配(%表示匹配所有字符，_表示只匹配一个字符) mysql> select name,salary*12 from emp where name like "jin%"; mysql> select name,salary*12 from emp where name like "jin_"; 4、正则匹配 mysql> select name,salary*12 from emp where name regexp "^jin.*"; 5、组合查询(and,or) mysql> select name,salary*12 from emp where (post='teacher' and age < 38) or salary not in (10000,9000,30000); 6、使用not来查询不满足某个条件的记录 mysql> select name,salary*12 from emp where not (post='teacher' and age < 38); group by 分组查询 mysql> select name,age,max(salary) from emp group by post; #按部门分组，取各部门里工资最高的人的姓名 mysql> select post,group_concat(name) from emp group by post; #按岗位分组，并查看组内所有成员名 mysql> select post,count(name) as 员工人数 from emp group by post; #按部门分组，取每个部门各有多少人 mysql> select sex,count(sex) as 人数 from emp group by sex; #按性别分组，取男女各多少人 mysql> select post,avg(salary) as 平均工资 from emp group by post; #按部门分组，取各部门的平均工资 mysql> select sex,avg(salary) as 平均工资 from emp group by sex; #按性别分组，取男女的平均工资 having 过滤 select count(id) as num,post,group_concat(name) as 员工名 from emp group by post having num < 2 mysql> select post,avg(salary) as 平均工资 from emp group by post having avg(salary) > 10000; select post,avg(salary) as 平均工资 from emp group by post having avg(salary) > 10000 and avg(salary) > 20000; select post,avg(salary) as 平均工资 from emp group by post having between 10000 and 20000; #错误的查询方法 order by 排序 select * from emp order by age asc,hire_date desc; #按照age升序排序，如果age相同则按照hire_date字段降序排序 select post,avg(salary) as 平均工资 from emp group by post order by avg(salary) asc; #各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 limit 限制查询的记录数 select * from emp limit 5; #查询结果只显示五条记录 select * from emp limit 0,5; #查询结果只显示前五条记录
6、查询练习表
#创建表 create table emp( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #往表中插入字段 insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龙','male',48,'20101111','teacher',10000,401,1), ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('丫丫','female',38,'20101101','sale',2000.35,402,2), ('丁丁','female',18,'20110312','sale',1000.37,402,2), ('星星','female',18,'20160513','sale',3000.29,402,2), ('格格','female',28,'20170127','sale',4000.33,402,2), ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3) ; 练习题： where 1. 查看岗位是teacher的员工姓名、年龄 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资 4. 查看岗位描述不为NULL的员工信息 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪 group by 1. 查询岗位名以及岗位包含的所有员工名字 2. 查询岗位名以及各岗位内包含的员工个数 3. 查询公司内男员工和女员工的个数 4. 查询岗位名以及各岗位的平均薪资 5. 查询岗位名以及各岗位的最高薪资 6. 查询岗位名以及各岗位的最低薪资 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 having 1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 3. 查询各岗位平均薪资大于10000的岗位名、平均工资 4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 order by 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 limit 1. 分页显示，每页5条 2. 查看所有员工中名字是jin开头，n或者g结果的员工信息
7、合并两次查询结果
SELECT * FROM (SELECT * FROM `testtable` WHERE name IS NOT NULL) AS temp UNION ALL (SELECT * FROM `testtable` WHERE name IS NULL) ORDER BY id DESC LIMIT 0,10; SELECT * FROM (SELECT * FROM `domain` WHERE nginx=1 AND addr="www") AS temp UNION ALL (SELECT * FROM `domain` WHERE nginx=2) ORDER BY id DESC LIMIT 0,10;
8、联表更新
#联表更新：(engineer e)重命名engineer表为e，(project p)project表为p UPDATE engineer e INNER JOIN project p ON e.`pid` = p.`id` SET e.username="小明",e.ctime=NOW(),e.status=1 WHERE p.title="test"; UPDATE fee_item f,sign s SET f.start_date ="2020-01-01",s.status =1 WHERE f.code = s.code AND s.merchant_id = "10";
# domainzone表为domain和zone的多对多关系表 SELECT site FROM domainzone INNER JOIN siteinfo ON domainzone.sid = siteinfo.`id` WHERE zid = (SELECT zoneinfo_id FROM project WHERE id="1"); SELECT site FROM domainzone INNER JOIN siteinfo ON domainzone.sid = siteinfo.`id` WHERE zid IN (SELECT zoneinfo_id FROM project);
10、表的导出和导入
1)、报错原因：mysql对此作了限制，只能将文件导出到/var/lib/mysql-files/目录
mysql> select * from db1.emp into outfile '/tmp/emp.txt' fields terminated by ',' lines terminated by '\r\n'; ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable mysql> SHOW VARIABLES LIKE "secure_file_priv"; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ # 修改配置文件 [root@Mysql ~]# vim /etc/my.cnf [mysqld] # secure_file_priv=NULL # 禁止导出 # secure_file_priv='' # 不限制导出目录 secure_file_priv='/tmp' # 只能只允将数据导出到/tmp目录下(PS：不允许导出到/tmp下的子目录) # 修改完后需要重启mysql
2)、导出文本文件（SELECT... INTO OUTFILE）
mysql> SELECT * FROM db1.emp INTO OUTFILE '/tmp/emp.txt' FIELDS TERMINATED BY ',' # 定义字段分隔符 OPTIONALLY ENCLOSED BY '"' # 定义字符串使用什么符号括起来 LINES TERMINATED BY '\n'; # 定义换行符
mysql命令导出文本文件
mysql -u root -p123 -e 'use db1;select * from emp' > /tmp/emp.txt mysql -u root -p123 -e 'select * from db1.emp' > /tmp/emp.txt mysql -u root -p123 --xml -e 'select * from db1.emp' > /tmp/emp.xml mysql -u root -p123 --html -e 'select * from db1.emp' > /tmp/emp.html
3)、导入文本文件（LOAD DATA INFILE）
mysql> DELETE FROM db1.emp; mysql> LOAD DATA INFILE '/tmp/emp.txt' INTO TABLE db1.emp FIELDS TERMINATED BY ',' # 定义字段分隔符 OPTIONALLY ENCLOSED BY '"' # 定义字符串使用什么符号括起来 LINES TERMINATED BY '\n'; # 定义换行符
4)、数据库迁移（务必保证在相同版本之间迁移）
mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456
11、Mysql命令tab键自动补全
[root@Mysql ~]# vim /etc/my.cnf
[mysql] #no-auto-rehash auto-rehash #开启tab键自动补全 # Remove the next comment character if you are not familiar with SQL #safe-updates #注意：自动补全是客户端命令，必须切换到数据库下才能使用，自动补全命令区分大小写
12、命令自动补全mycli工具安装
[root@Mysql ~]# yum -y install epel-release python-pip python-devel
[root@Mysql ~]# pip install --ignore-installed configobj
[root@Mysql ~]# pip install mycli
[root@Mysql ~]# mycli #使用mycli命令登陆mysql
mysql> help select; #MySQL命令帮助查询
#删除nginxinfo表两天前的数据(ctime为时间列) mysql -h x.x.x.x -uroot -p123456 -D test -e "DELETE FROM nginxinfo WHERE DATE(ctime) <= DATE(DATE_SUB(NOW(),INTERVAL 2 DAY));" #将两次查询结果合并 SELECT * FROM (SELECT * FROM `testtable` WHERE name IS NOT NULL) AS temp UNION ALL (SELECT * FROM `testtable` WHERE name IS NULL) LIMIT 0,10;
13、adminer、phpmyadmin docker部署
root@Ubuntu:~# docker run --name adminer -p 8080:8080 -d --restart=always adminer root@Ubuntu:~# docker run --name myadmin -d -e PMA_ARBITRARY=1 -p 8080:80 --restart=always phpmyadmin
14、Mysql错误代码
# MySQL错误代码为1728的问题以及解决方案 root@mariadb:/# mysql -p123456 < username.sql ERROR 1728 (HY000) at line 4: Cannot load from mysql.proc. The table is probably corrupted root@mariadb:/# mysql_upgrade -uroot -p123456
参考链接：
https://www.cnblogs.com/tim1blog/p/9757102.html #mycli安装
https://www.cnblogs.com/xiaoxi/category/892035.html #mysql查询
https://www.adminer.org/en/#download #adminer数据库管理工具
https://docs.phpmyadmin.net/zh_CN/latest/intro.html #aphpmyadmin数据库管理工具
https://hub.docker.com/_/phpmyadmin?tab=description&page=1&ordering=last_updated
https://www.cnblogs.com/ritchy/p/12094431.html # MySQL表的导入和导出
