数据库小结
时间：2021-07-01 10:21:17
帮助过：31人阅读
备份所有数据库: mysqldump -u root -p --all-databases > all_database_sql
备份mysql数据库：mysqldump -u root -p --database mysql > mysql_database_sql
备份指定的多个数据库：mysqldump -u root -p --database db1 db2 db3 > bak.sql
备份mysql数据库下的user数据包：mysqldump -u root -p mysql user > user_table
把备份的所有数据文件还原：mysql -u root -p < all_database_sql，其他类似
二. 数据库查询语言
1）查询employees数据表中的所有数据记录: select * from employees;
2）查询employees表中的employee_id, first_name记录：select employee_id,first_name from employees;
3）查询test表中score列数据的所有平均值：select AVG(score) from test;
4）统计有多少条employee_id记录，显示最终统计个数：select count(employee_id) from employees;
5）如果有多条相同的email记录，DISTINCT可以去除重复记录：select distinct(e_mail) from employees;
6）查询last_name为willian的所有记录，并显示出相应的first_name记录：select first_name from employees where last_name="willian";
7）查询表中所有记录，按周hire_date列排序，DESC为降序，AES为升序：select * from employees order by hire_date DESC;
8）限制仅显示数据记录中的前两行记录：select * from employees limit 2;
三.数据库定义语言
1. 新建数据库：create database hr;
2. 显示所有数据库：show databases;
3. 新建数据库中的表：首先进入相应的数据库 use hr;
![技术分享]()
其中ENGINE=innodb DEFAULT CHARSET=UTF8; #设置默认存储引擎和默认字体，也可单独写
4. show tables;查看数据表
5. 查看数据表的数据结构 ：describe employee;
6. alter database:修改数据库属性，属性被保存在数据库目录的db.opt文件中。
例如：修改数据库默认字符集和排序规则
alter database hr default character set=utf8;
alter database hr default collate=utf8_general_ci;
7. 修改数据表结构，如添加删除的列，创建删除的索引，修改数据类型等
例如：创建数据表test1后，通过alter语句修改数据表的相关信息
create table test1(id int, name char(20));
alter table test1 rename test2; #修改test1名字为test2
alter table test2 add date timestamp; #添加date项，类型为时间戳
alter table test2 add note char(50); #增加note项
alter table test2 add index (date); #给date项添加普通索引
alter table test2 modify id tinyint not null, change name first_name char(20); #修改id类型为tinyint，并设为非空，修改name值为first_name
alter table test2 add primary key(id); #给id添加主索引
alter table test2 drop column note; #删除note行
8. 删除名为hr的数据表：drop table hr;
9 对一个或多个数据表重命名
rename table test1 to temp, test3 to test1, temp to test3;
10. 删除数据库及当中的数据表
drop database hr;
11. 删除索引
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：
drop index index_name on table_name ; #一般用这个就足够了
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。
如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
例如：
![技术分享]()
使用命令 mysql> drop index date on test2;
![技术分享]()
四. 数据库操作语言
1. insert
结果如下
![技术分享]()
2. update
用来更新数据表中现有的数据值（仅修改满足where条件的数据记录）
update hr.employee set name="luck" where id=3; #修改lucky为luck
3. load data infile
快速从文本文件中读取数据到数据表中
分隔符：文件默认使用Tab键位列分隔符，换行符为行分隔符，可以使用FIELDS TERMINATED BY 设置列分隔符，通过LINES STARTING BY设置行分隔符。
例如：提前创建数据文件/tmp/txt与txt2，通过LOAD语句加载数据到employee数据表
cat /tmp/txt
004 ellis ellis@gmail.com sale
mysql> load data infile ‘/tmp/txt‘ into table hr.employee;
cat /tmp/txt2
005,berry,berry@gmail.com,admin
mysql> load data infile ‘/tmp/txt2‘ into table hr.employee fields terminated by ‘,‘;
结果如下
![技术分享]()
4.delete
把满足条件的数据记录删除并返回删除的记录数量，如下
mysql> use hr;
mysql> delete from employee where id=5;
mysql> delete from employee where name="ellis";
五. 添加删除用户，授权，修改用户密码
1. 创建用户:
命令:CREATE USER ‘username‘@‘host‘ IDENTIFIED BY ‘password‘;
例子: CREATE USER ‘hong‘@‘localhost‘ IDENTIFIED BY ‘123456‘;
CREATE USER ‘cat‘@‘localhost‘ IDENTIFIED BY ‘‘;
注意：
username - 你将创建的用户名,
host - 指定该用户在哪个主机上可以登陆，此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录；
如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录;
也可以指定某台机器可以远程登录，此时用指定机器的ip;
password - 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器。
2. 授权:
命令:GRANT privileges ON databasename.tablename TO ‘username‘@‘host‘
注意:
privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL.;
databasename - 数据库名,
tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*.
例子: GRANT SELECT, INSERT ON mq.* to ‘hong‘@‘localhost‘;
3. 创建用户同时授权
1.创建tomcat账户，该账户仅可以通过本机连接mysql服务器，对hr数据库中的所有数据表拥有所有权限
mysql> grant all privileges on hr.* to ‘tomcat‘@‘localhost‘ identified by ‘1234‘; #其中privileges要不要都行
2. 创建tomcat账户，此账户只能从主机192.168.0.88连接到服务器，对hr数据库中的所有数据表仅有查询权限
mysql > grant select on hr.* to ‘tomcat‘@’192.168.0.88‘ identified by ‘pass‘;
3. 创建admin账户，赋予该账户对所有数据库的管理权限，不需密码即可从本机连接mysql服务器
mysql>grant reload,process on *.* to ‘admin‘@‘localhost‘;
4. 创建jacob账户，赋予jacob对hr数据库中所有数据表的所有权限，该账户可从任何主机连接服务器
mysql> grant all on hr.* to ‘jacob‘@‘%‘ identified by ‘pass‘;
mysql> flush privileges;
PS:必须执行flush privileges;
否则登录时提示：ERROR 1045 (28000): Access denied for user ‘user‘@‘localhost‘ (using password: YES )
4. 设置与更改用户密码的3种方式
1.通过命令:SET PASSWORD设置密码
格式：SET PASSWORD FOR ‘username‘@‘host‘ = PASSWORD(‘newpassword‘);
例子: SET PASSWORD FOR ‘admin‘@‘localhost‘ = PASSWORD("321");
2. 直接修改数据库权限数据表mysql.user
mysql>update mysql.user set password=password(‘新密码‘) where User="admin" and Host="localhost";
mysql>flush privileges;
注意：MySQL5.7 mysql.user表没有password字段改 authentication_string；
3. 通过mysqladmin设置账户密码
mysqladmin -u root -p password ‘123‘
-p的作用是提示输入旧密码，若没有旧密码，可忽略。
5. 撤销用户权限
命令: REVOKE privilege ON databasename.tablename FROM ‘username‘@‘host‘;
例子: REVOKE SELECT ON hr.* FROM ‘admin‘@‘localhost‘;
revoke all on hr.* from tomcat@‘%‘; #撤销tomcat对hr数据库的所有权限
revoke select on *.* from jacob@‘%‘; #撤销jacob对所有数据库的查询权限
注意:
假如你在给用户‘hong‘@‘localhost‘‘授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO ‘hong‘@‘localhost‘, 则在使用REVOKE SELECT ON *.* FROM ‘hong‘@‘localhost‘;命令并不能撤销该用户对test数据库中user表的SELECT 操作.
相反,如果授权使用的是GRANT SELECT ON *.* TO ‘hong‘@‘localhost‘;则REVOKE SELECT ON test.user FROM ‘hong‘@‘localhost‘;命令也不能撤销该用户对test数据库中user表的Select 权限.
具体信息用命令SHOW GRANTS FOR ‘hong‘@‘localhost‘; 查看.
6.删除用户
命令: DROP USER ‘username‘@‘host‘;
比如：mysql> drop user jerry@‘172.16.0.253‘;
有时候可能需要先查看用户有哪些，命令如下
mysql> use mysql;
mysql> select host,user from mysql.user;
7.查看用户的授权
mysql> show grants for hong@localhost;
+---------------------------------------------+
| Grants for dog@localhost |
+---------------------------------------------+
| GRANT USAGE ON *.* TO ‘hong‘@‘localhost‘ |
| GRANT INSERT ON `mq`.* TO ‘hong‘@‘localhost‘ |
+---------------------------------------------+
2 rows in set (0.00 sec)
注意：GRANT USAGE: usage权限就是空权限，默认create user的权限，只能连库，啥也不能干
六. MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
1、数据库名与表名是严格区分大小写的；
2、表的别名是严格区分大小写的；
3、列名与列的别名在所有的情况下均是忽略大小写的；
4、变量名也是严格区分大小写的；
比如select * from mysql.user;命令中，User，Host均是列名，所以大小写忽略。
七. 数据表的常用数据类型和常用属性
常用数据类型
常用属性
八. 用户权限
数据库小结
标签：用户密码 别名 数据结构 ati 小结 hang innodb 没有 font
