MySQL数据库管理与高级操作

1. mysql基本操作

 1.查看数据库信息

[root@server ~]# mysql -uroot -pabc123	#登录数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |		#定义访问数据库单元数据的方式。数据库名和表名，列的数据类型，访问权限等。
| auth               |
| bbs                |
| mysql              |		#核心数据库，负责存储数据库用户、权限、关键字等用户自己需要使用的控制和管理信息。
| performance_schema |		#数据库的性能参数，存储引擎等。
| sys                |		#sys系统库下包含许多试图，它们以各种方式对performance_schema表进行聚合计算展示。
| tanwenlong         |
+--------------------+
7 rows in set (0.00 sec)

 2.查看当前数据库中有哪些表
mysql> use mysql;		#use 数据库名；
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;		#查看数据库中的表
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| func                      |
| general_log               |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
23 rows in set (0.00 sec)
## 3.查看表的结构
mysql> use mysql;
mysql> describe  user;
## 4.SQL语句概述
### 1、SQL语言
	1、Structured Query Language的缩写，即结构化查询语言
	2、关系型数据库的标准语言
	3、用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能
### 2、SQL分类
	1、DDL（Data　Definition　Language）数据定义语言：用来建立数据库、数据库对象和定义字段，如CREATE、ALTER、DROP
	2、DML（Data　Manipulation　Language）数据操纵语言：用来插入、删除和修改数据库中的数据，如INSERT、UPDATE、DELETE
	3、DQL（Data　Query　Language）数据查询语言：用来查询数据中的数据，如SELECT
	4、DCL（Data　Control　Language）数据控制语言：用来控制数据库组件的存取许可、存取权限等，如COMMIT、ROLLBACK、GRANT、REVOKE
## 5.DDL（Data　Definition　Language）数据定义语言
#############1.创建新的数据库和新的表#######################
mysql> create database twl;		#CREATE DATABASE 数据库名
mysql> use twl;
mysql> create table users(name char(26) not null,passwd char(36) default'123456',primary key(name));
#############5.2删除一个数据表############################	
mysql> drop table user;
#############5.3删除一个数据库############################
mysql> drop database twl;
## 6.DML（Data　Manipulation　Language）数据操纵语言
#############6.1创建数据库与数据表#########################
mysql> create database twl;
Query OK, 1 row affected (0.00 sec)

mysql> use twl;
Database changed
mysql> create table users(name char(26) not null,passwd char(36) default'123456',primary key(name));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_twl |
+---------------+
| users         |
+---------------+
1 row in set (0.00 sec)

mysql> describe users;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(26) | NO   | PRI | NULL    |       |
| passwd | char(36) | YES  |     | 123456  |       |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
##############6.2插入数据记录###########################
mysql> use twl;
mysql> insert into users values('zhangsan',default);
mysql>  insert into users(name,passwd) values('lisi','123456');    ###INSERT INTO 表名（字段1，字段2…） VALUES（字段1的值，字段2的值…
#############6.3查询数据记录############################
mysql> select * from twl.users;    
+----------+--------+
| name     | passwd |
+----------+--------+
| lisi     | 123456 |
| zhangsan | 123456 |
+----------+--------+
2 rows in set (0.00 sec)
mysql> select name,passwd from twl.users where name='zhangsan';		###SELECT 字段名1，字段名2，… FROM 表名 WHERE 条件表达式
##########6.4修改数据记录############################
mysql> update twl.users set passwd=123123 where name='zhangsan';  ###UPDATE 表名 SET 字段名1=字段值1[字段名2=字段值2] WHERE 条件表达式
##########6.5修改数据库密码##########################
方法一：
mysql> update mysql.user set authentication_string=PASSWORD('123456') where user='root';
mysql> flush privileges;
方法二：
[root@server ~]# mysqladmin -u root -p'123456' password 'abc123'
方法三：
mysql> SET PASSWORD = PASSWORD('newpass'); //root用户
mysql> SET PASSWORD FOR 用户 = PASSWORD(‘newpass');
忘记密码：
vi /etc/my.cnf
[mysqld] 
末尾添加
skip_name_resolve
skip_grant_tables
bind-address=0.0.0.0
systemctl restart mysqld
##########6.6删除数据库的记录##########################
mysql> delete from twl.users where name='zhangsan';  ###DELETE FROM 表名 WHERE 条件表达式
mysql> select * from twl.users;

2.数据表的高级操作

#########1.1清空表############
mysql> delete from twl.users;		###DELETE语句可以通过WHERE对要删除的记录进行选择。数据没了，结构还在。
mysql> truncate table twl.users;	###TRUNCATE TABLE将删除表中的所有记录。数据没了，结构还在。
########1.2创建临时表##########
##########临时建立的表，用于保存一些临时数据，不会长期存在###################
mysql> CREATE TEMPORARY TABLE `lt`(`id` int(10) NOT NULL AUTO_INCREMENT,`ame name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`level` int(10) NOT NULL,PRIMARY KEY (id))ENGINE=InnoDB  DEFAULT COLLATE=utf8;		###TEMPORARY:临时，AUTO_INCREMENT：自加一，CHARACTER SET utf8字符集设置utf8,COLLATE utf8_bin NOT NULL:编码设置二进制编码,DEFAULT COLLATE=utf8:默认字符集
mysql> INSERT INTO lt(name,level) VALUES('aa',10);
mysql> select * from lt; 	###临时建立，退出删除，在show tables;看不到
#########1.3克隆表#############
#####LIKE方法：#####
从ttt完整复制结构生成test1表，再导入数据
mysql> create table test1 like ttt;
mysql> insert into test1 select * from ttt;	#插入信息
####SHOW CREATE TABLE方法####
先查看bbb表完整结构，根据此结构创建名字不同结构相同的表test2，再导入数据
mysql> show create table bbb\G
mysql> create table test2("id" int(20) NOT NULL AUTO_INCREMENT,   "name" varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,   "level" int(20) NOT NULL,   PRIMARY KEY ("id") ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
mysql>insert into test2 select * from bbb;
######1.4数据库用户授权##########
mysql> insert into users(name,passwd) VALUES('lisi',PASSWORD'123123');
mysql> GRANT select ON twl.users TO 'lisi'@'localhost' IDENTIFIED BY '123456';   ###语法：GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 IDENTIFIED BY ‘密码’
#####1.5查看用户权限############
mysql> SHOW GRANTS FOR 'lisi'@'localhost';     ###SHOW GRANTS FOR 用户名@来源地址
mysql> exit
mysql -ulisi -p 123456
#######1.6撤销用户的权限#######
mysql> REVOKE all ON twl.users FROM 'lisi'@'localhost'; ###REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址