目录
一、数据库基本操作
1.1 查看数据库结构
■查看数据库信息
[root@localhost ~]# mysql -uroot -p
Enter password: '//输入密码'
mysql> show databases; '//必须以;结束,也可以先不输入直接按回车在下一行输入,输入的内容也可以是大写字母'
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
■查看数据库中的表信息
mysql> use mysql; '//数据库名'
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 |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| 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 |
+---------------------------+
31 rows in set (0.00 sec)
mysql>
■显示数据库的结构(字段)
mysql> describe user; '//数据库名或表名,也可以使用desc user;'
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N
1.2 SQL语句概述
■SQL语言
●Structured Query Language的缩写,即结构化查询语言
●关系型数据库的标准语言
●用于维护管理数据库
◆包括数据查询、数据更新、访问控制、对象管理等功能
■SQL分类
●DDL:数据定义语言 create(表、库),drop,alter(表结构、属性、引擎)
●DML:数据操纵语言 insert into、update、delete(数据)
●DQL:数据查询语言 select
●DCL:数据控制语言 grant (授权)、revoke(撤权)
1.3 创建数据库和表(DDL)
■DDL语句可用于创建数据库对象,如库、表、索引等
■使用DDL语句新建库、表
●创建数据库
◆create database 数据库名
mysql> create database auth;
Query OK, 1 row affected (0.00 sec)
●创建数据表
◆create table 表名 (字段定义)
mysql> use auth; '//进入数据库'
Database changed
mysql> create table users(user_name CHAR(16) NOT NULL,user_passwd CHAR(48) DEFAULT '',PRIMARY KEY(user_name)); '//创建数据表,初始密码为空'
Query OK, 0 rows affected (0.01 sec)
■使用DDL语句删除库、表
●删除指定的数据表
◆DROP TABLE [数据库名] 表名
mysql> drop table auth.users; '//删除数据表'
Query OK, 0 row affected(0.00 sec)
●删除指定的数据库
◆DROP DATABASE 数据库名
mysql> drop database auth; '//删除数据库'
Query OK, 0 row affected(0.00 sec)
1.4 管理表中的数据
1.4.1 DML数据查询语句
■DML语句用于对表中的数据进行管理
■包括的操作
●INSERT:插入新数据
●UPDATE:更新原有数据
●DELETE:删除不需要的数据
■向数据表中插入新的数据记录
●INSERT INTO 表名(字段1,字段2,…) VALUES(字段1的值,字段2 的值,…)
mysql> use auth '//进入数据库'
mysql> insert into users(user_name,user_passwd) values('zhangsan',password('Abc123'));
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into users values('lisi',password('Abc123'));
Query OK, 1 row affected, 1 warning (0.00 sec)
'//如果这条记录包含表中所有字段的值,则插入语句中的指定字段可以省略'
mysql> select * from auth.users; '//查看表中的数据'
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
| zhangsan | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> select user_name from auth.users; '//查看指定的字段名称'
+-----------+
| user_name |
+-----------+
| lisi |
| zhangsan |
+-----------+
2 rows in set (0.00 sec)
mysql> select user_passwd from auth.users; '//查看指定字段密码'
+-------------------------------------------+
| user_passwd |
+-------------------------------------------+
| *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
| *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+-------------------------------------------+
2 rows in set (0.01 sec)
■修改、更新数据表中的数据记录
●UPDATE 表名 SET 字段名1=值1[,字段名2=值2] WHERE 条件表达式
mysql> update auth.users set user_passwd=password('123456') where user_name='lisi'; '//修改lisi的密码为123456'
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges; '//刷新'
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhangsan | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
'//密码已经修改'
mysql> update mysql.user set authentication_string=PASSWORD('abc123') where User='root';
'//mysql5.7版本修改root命令'
mysql> flush privileges; '//刷新下'
■在数据表中删除指定的数据记录
●DELETE FROM 表名 WHERE 条件表达式
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhangsan | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> delete from auth.users where user_name='lisi'; '//删除lisi的数据记录'
Query OK, 1 row affected (0.00 sec)
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| zhangsan | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
'//发现没有了lisi的数据信息'
1.4.2 DQL数据查询语句
■DQL是数据查询语句,只有SELECT
■用于从数据表中查找符合条件的数据记录
■查询时可不指定条件
●SELECT 字段名1,字段名2,…FROM 表名
mysql> select * from auth.users; '//查看表中的数据'
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
| zhangsan | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
■查询时指定条件
mysql> select user_name,user_passwd from auth.users where user_name='zhangsan';
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| zhangsan | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select user_name from auth.users; '//查看指定的字段名称'
+-----------+
| user_name |
+-----------+
| lisi |
| zhangsan |
+-----------+
2 rows in set (0.00 sec)
mysql> select user_passwd from auth.users; '//查看指定字段密码'
+-------------------------------------------+
| user_passwd |
+-------------------------------------------+
| *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
| *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+-------------------------------------------+
2 rows in set (0.01 sec)
1.5 数据库表高级操作
1.5.1 清空表
mysql> delete from auth.users; '//清空表中所有数据,谨慎操作!'
Query OK, 1 row affected (0.00 sec)
'//或者可以:'
mysql> TRUNCATE TABLE auth.users;
1.5.2 临时表
■临时建立的表,用于保存一些临时数据,不会长期存在,创建临时表mytmp,之后断开连接,最后重新连接到mysql查看临时表是否存在
mysql> CREATE TEMPORARY TABLE `mytmp` (
-> `id` int(10) NOT NULL AUTO_INCREMENT,
-> `NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin
-> NOT NULL,
-> `level` int(10) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
'//创建临时表mytmp'
mysql> insert into mytmp(name,level) values('aa',10);
Query OK, 1 row affected (0.00 sec) '//插入数据'
mysql> select * from auth.mytmp; '//查看内容'
+----+------+-------+
| id | NAME | level |
+----+------+-------+
| 1 | aa | 10 |
+----+------+-------+
1 row in set (0.00 sec)
mysql> exit '//退出数据库'
[root@localhost ~]# mysql -uroot -p
Enter password: '//输入密码重新进入'
mysql> select * from auth.mytmp; '//查看数据表,发现没有这个表'
ERROR 1146 (42S02): Table 'auth.mytmp' doesn't exist
''//临时表创建成功后,使用show tables命令无法查看临时表,临时表会在连接退出后被销毁,若在退出之前也可以手动删除,使用DROP TABLE语句,具体操作如下'
mysql> drop table mytmp;
1.5.3 克隆表
■like方法
mysql> use auth;
mysql> create table test like users; '//从users完整复制结构生成test表'
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test select * from users; '//导入users里的数据进test'
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from auth.test; '//查看test中的数据,与users中的数据一样'
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
| zhangsan | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
■SHOW CREATE TABLE方法
●通过创建表的方式克隆表
◆首先,使用SHOW CREATE TABLE命令来获取源表的表结构、索引等信息;其次,复制源表结构并修改表名为目标名字,然后执行创建新表的语句,通过这步操作,就可以获得一个和源表结构一样的克隆表;最后,执行INSERT INTO…SELECT语句,从源表复制数据到新表内。
mysql> show create table users\G '//查看users表的完整结构'
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE "test" (
"user_name" char(16) NOT NULL,
"user_passwd" char(48) DEFAULT '',
PRIMARY KEY ("user_name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> create table test1(user_name CHAR(16) NOT NULL,user_passwd CHAR(48) DEFAULT '',PRIMARY KEY(user_name));
'//创建test1表,与users表结构相同'
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 select * from users; '//导入users中的数据到test1中'
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test1; '//查看test1中的数据,与users中的数据一样'
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
| zhangsan | *7A8BBCB18A250055A6BB98ECFA33A8174D219504 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
二、数据库用户授权
2.1 DCL语句设置用户权限并查看权限(用户不存在时,创建用户)
■GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [IDENTIFIED BY ‘密码’]
mysql> GRANT select ON auth.* TO 'tom'@'localhost' IDENTIFIED BY 'Abc123'; '//创建tom用户名,并设置权限为仅选'
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW GRANTS FOR 'tom'@'localhost'; '//查看用户权限'
+-----------------------------------------------+
| Grants for tom@localhost |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost' |
| GRANT SELECT ON "auth".* TO 'tom'@'localhost' |
+-----------------------------------------------+
2 rows in set (0.00 sec)
2.2 撤销用户授权
■REVOKE权限列表 ON 数据库名.表名 FROM 用户名@来源地址
mysql> REVOKE all ON auth.* from 'tom'@'localhost'; '//撤销权限'
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'tom'@'localhost'; '//再次查看权限,发现没有SELECT权限'
+-----------------------------------------+
| Grants for tom@localhost |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)
'//若要授权所有权限,将SELECT换成ALL即可'
三、总结
■数据库的基本操作命令
●查看数据库、数据表和表结构的操作
●创建库和表的操作及删除库和表的操作
●数据表的增、删、查、改等操作
●数据库表的清空表、临时表可克隆表的操作
■数据库的用户授权相关操作