MySQL单表查询
单表查询
======================================
准备测试表:company.employee5
雇员编号 id int
雇员姓名 name varchar(30)
雇员性别 sex enum
雇用时期 hire_date date
职位 post varchar(50)
职位描述 job_description varchar(100)
薪水 salary double(15,2)
办公室 office int
部门编号 dep_id int
mysql> CREATE TABLE company.employee5(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null,
sex enum('male','female') default 'male' not null,
hire_date date not null,
post varchar(50) not null,
job_description varchar(100),
salary double(15,2) not null,
office int,
dep_id int
);
mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('tianyun','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);
=========================================================================================
mysql> select 字段名称,字段名称2 from 表名 条件
mysql>select column_name,column_2 from table where ...
简单查询:
mysql> select * from t3;
mysql> select name, salary, dep_id from employee5;
避免重复DISTINCT
SELECT post FROM employee5;
SELECT DISTINCT post FROM employee5;
通过四则运算查询
运算:
mysql>select 437.4384/5;
mysql>select 5>3;
SELECT name, salary, salary*14 FROM employee5;
SELECT name, salary, salary*14 AS Annual_salary FROM employee5;
SELECT name, salary, salary*14 Annual_salary FROM employee5;
定义显示格式
CONCAT() 函数用于连接字符串
SELECT CONCAT(name, ' annual salary: ', salary*14) AS Annual_salary FROM employee5;
单条件查询
mysql> select name from employee5 where salary=5000;
多条件查询
mysql> select name from employee5 where salary>5000 and salary<6000;
关键字BETWEEN AND
SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;
关键字IS NULL
SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;
SELECT name,job_description FROM employee5 WHERE job_description='';
NULL说明:
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
3、对空值做加、减、乘、除等运算操作,结果仍为空。
4、比较时使用关键字用“is null”和“is not null”。
5、排序时比其他数据都小,所以NULL值总是排在最前。
关键字IN集合查询
SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;
SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;
SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000) ;
排序查询
mysql> select name from employee5 order by name;
mysql> select name from employee5 order by name desc;
mysql> select name from employee5 order by name desc limit 3; //限制次数
mysql> select name from employee5 order by name desc limit 1,3;
mysql> select name from employee5 order by name desc limit 2,3;
注:
ascending 美音 /ə'sɛndɪŋ/ 升序
descending 美音 /dɪ'sɛndɪŋ/ 降序
按多列排序:
入职时间相同的人薪水不同
SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;
有差别于
select * from employee5 ORDER BY hire_date DESC;
先按入职时间,再按薪水排序
select * from employee5 ORDER BY hire_date DESC,salary DESC;
先按职位,再按薪水排序
select * from employee5 ORDER BY post,salary DESC;
限制查询的记录数
SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5; //默认初始位置为0
SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3,5; //从第4条开始,共显示5条
分组查询
GROUP BY和GROUP_CONCAT()函数一起使用
SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;
模糊查询(通配符)
% 所有字符
mysql> select * from employee5 where salary like '%20%';
正则查询
select * from employee5 where salary regexp '72+';
SELECT * FROM employee5 WHERE name REGEXP '^ali';
SELECT * FROM employee5 WHERE name REGEXP 'yun$';
SELECT * FROM employee5 WHERE name REGEXP 'm{2}';
函数
count()
max()
min()
avg()
database()
user()
now()
sum()
password()
SELECT COUNT(*) FROM employee5;
SELECT COUNT(*) FROM employee5 WHERE dep_id=101;
SELECT MAX(salary) FROM employee5;
SELECT MIN(salary) FROM employee5;
SELECT AVG(salary) FROM employee5;
SELECT SUM(salary) FROM employee5;
SELECT SUM(salary) FROM employee5 WHERE dep_id=101;
MySQL多表查询
多表查询
多表连接查询
一、准备两张测试表
表company.employee6
mysql> create table employee6(
emp_id int auto_increment primary key not null,
emp_name varchar(50),
age int,
dept_id int);
mysql> desc employee6;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| emp_id | int(11) | NO | PRI | NULL | auto_increment |
| emp_name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| dept_id | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
mysql> insert into employee6(emp_name,age,dept_id) values
('tianyun',19,200),
('tom',26,201),
('jack',30,201),
('alice',24,202),
('robin',40,200),
('natasha',28,204);
mysql> select * from employee6;
+--------+----------+------+---------+
| emp_id | emp_name | age | dept_id |
+--------+----------+------+---------+
| 1 | jrev | 19 | 200 |
| 2 | tom | 26 | 201 |
| 3 | jack | 30 | 201 |
| 4 | alice | 24 | 202 |
| 5 | robin | 40 | 200 |
| 6 | natasha | 28 | 204 |
+--------+----------+------+---------+
表company.department6
mysql> create table department6(
dept_id int,
dept_name varchar(100)
);
mysql> desc department6;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| dept_id | int(11) | YES | | NULL | |
| dept_name | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
mysql> insert into department6 values
(200,'hr'),
(201,'it'),
(202,'sale'),
(203,'fd');
mysql> select * from department6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 200 | hr |
| 201 | it |
| 202 | sale |
| 203 | fd |
+---------+-----------+
注:
Financial department:财务部门 fd
二、多表的连接查询
使用where条件
mysql> select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name
from employee6,department6
where employee6.dept_id = department6.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | jrev | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
| 4 | alice | 24 | sale |
| 5 | robin | 40 | hr |
+--------+----------+------+-----------+
使用别名:
> select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a,departmant6 b where a.dept_id = b.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | jrev | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
| 4 | alice | 24 | sale |
| 5 | robin | 40 | hr |
+--------+----------+------+-----------+
Mysql安全控制
-
确保MySQL运行用户为一般用户
# groupadd mysql # useradd -M -s /sbin/nologin -g mysql mysql # vim /etc/my.cnf user = mysql #注意点: 改变拥有者和所属组对于mysql的安装目录
-
建议修改默认端口3306,改为其他的一些端口
# vim /etc/my.cnf port = 3306 false port = 10086 true
-
开启mysql二进制日志,在误删除数据的情况下,可以通过二进制日志恢复到某个时间点
# vim /etc/my.cnf log_bin = othername
4.删除空口令账号
#禁用匿名账号 # vim /etc/my.cnf skip-grant-tables = 1. --改成 "#skip-grant-tables = 1" #删除空口令用户 mysql> select User,Host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | User | Host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | +---------------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec) mysql> create user 'jrev'@'localhost'; #(这是在做实验)创建空口令账户 Query OK, 0 rows affected (0.00 sec) mysql> select User,Host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | User | Host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | youngfit | localhost | | +---------------+-----------+-------------------------------------------+ 4 rows in set (0.00 sec) mysql> drop user 'jrev'@'localhost'; #这是删除空口令账户 Query OK, 0 rows affected (0.01 sec) mysql> select User,Host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | User | Host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | +---------------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec)
-
禁止root账户远程访问(允许普通用户远程访问,某个网段即可)
mysql> select User,Host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | User | Host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | +---------------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec) mysql> create user 'root'@'10.0.11.%' identified by "123"; Query OK, 0 rows affected (0.00 sec) mysql> select User,Host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | User | Host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | root | 10.0.11.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | +---------------+-----------+-------------------------------------------+ 4 rows in set (0.00 sec) mysql> drop user 'root'@'10.0.11.%'; Query OK, 0 rows affected (0.00 sec)
-
使用mysql的时候,经常会遇到
MySQL: ERROR 1040: Too many connections
这样的问题,一种是访问量确实很高, MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力,另外一种情况是MySQL配置文件中max_connections值过小, 这时就需要调整当前最大连接数
##设置最大连接数02 修改mysql配置文件my.cnf,在[mysqld]段中添加或修改max_connections值: max_connections=256
-
DCL(Data Control Language 数据库控制语言)
用于数据库授权、角色控制等操作
GRANT
授权,为用户赋予访问权限
REVOKE
取消授权,撤回授权权限
用户管理
登录和退出MySQL
远程登陆:
客户端语法:mysql -u 用户名 -p 密码 -h ip地址 -P端口号:如果没有改端口号就不用-P指定端口
# mysql -h192.168.62.148 -P 3306 -uroot -p123
创建用户
create user '用户名'@'客户端来源IP地址' identified by '密码';
mysql> create user youngfit@'192.168.62.%' identified by '123';
删除用户
drop user '用户名'@'客户端来源IP地址';
mysql> drop user youngfit@'192.168.62.%';
修改用户
rename user '用户名'@'客户端来源IP地址' to '新用户名'@'客户端来源IP地址' ;
mysql> rename user youngfit@'192.168.62.%' to ehome@'%';
修改密码
// 第一种方法:
set password for '用户名'@'IP地址'=Password('新密码')
mysql> set password for ehome@'%'=Password('123');
// 第二种方法:
alter user '用户名'@'客户端来源IP地址' identified by '新密码';
// 第三种方法(忘记密码时,必须使用此方法修改密码):
UPDATE mysql.user SET authentication_string=password('QFedu123!') WHERE user='root' and host='localhost';
===root修改自己密码
# mysqladmin -uroot -p'123' password 'new_password' //123为旧密码
案例:
# mysqladmin -uroot -p'123' password 'jrev@123';
PS:用户权限相关数据保存在mysql数据库的user表中,所以也可以直接对其进行操作(不建议)
权限管理
grant 权限 on 数据库.表 to '用户'@'客户端来源IP地址' identified by '密码'; -- 授权并设置密码
revoke 权限 on 数据库.表 from '用户'@'客户端来源IP地址' -- 取消权限
mysql> grant all privileges on company.* to ehome@'%';
mysql> revoke all privileges on company.* from ehome@'%';
mysql > flush privileges; #关于权限方面的修改,注意刷新权限,否则有可能不生效
查看授权信息
查看授权语句
show grants for '用户'@'客户端来源IP地址';
mysql> show grants for ehome@'%';
+-----------------------------------+
| Grants for ehome@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'ehome'@'%' |
+-----------------------------------+
1 row in set (0.00 sec
查看生效的授权信息
针对所有库和表的权限,比如 *.*
。 去 mysql.user
中查看
select * from mysql.user where user='root'\G
mysql> select * from mysql.user where user='ehome'\G
*************************** 1. row ***************************
Host: %
User: ehome
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
password_expired: N
password_last_changed: 2019-08-20 19:35:41
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
针对具体到库的权限,比如db_name.*
。 去 mysql.db
中查看
mysql> select * from mysql.db where user='ehome'\G
*************************** 1. row ***************************
Host: %
Db: company
User: ehome
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
1 row in set (0.00 sec)
假如是 MySQL8.x
CREATE USER '你的用户名'@'localhost' IDENTIFIED BY '你的密码';
#创建新的用户
GRANT ALL PRIVILEGES ON 你的数据库名.* TO '你的用户名'@'localhost';
#把刚刚创建的数据库的管理权限给予刚刚创建的MySQL用户
FLUSH PRIVILEGES;
#刷新权限,使用设置生效