一.常规操作命令
1.1 设置root 用户密码，及登录
[root@shanan mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
[root@shanan ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rightsreserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
1.2查看数据库信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
mysql> use mysql; 进入mysql 数据库
Database changed
mysql> show tables; 查看数据库中的表信息
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
1.3 查看数据库中的表结构
ysql> mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
用此命令 效果一样
mysql> 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 | |
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
1.4 SELECT查看用户信息表
DQL是数据查询语句，只有一条：SELECT
用于从数据表中查找符合条件的数据记录
不指定条件查询命令是
SELECT字段名1，字段名2…FROM表名
mysql> select * from user;
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv| Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *204C8A3B79DC1533866049F6819865291C5E6AD5 | N | 2020-08-15 10:05:06 | NULL | N |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N| N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2020-08-15 10:00:43 | NULL | Y |
+-----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
2 rows in set (0.00 sec)
1.4.1 查看几个用户
mysql> select user from user;
+-----------+
| user |
+-----------+
| mysql.sys |
| root |
+-----------+
2 rows in set (0.00 sec)
mysql>
1.5 创建数据库 school
mysql> create database school;
查看数据库列表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use school;
Database changed
mysql> show tables;
Empty set (0.00 sec)
1.5.1定义表字段
**mysql> create table info (id int(3) not null primary key auto_increment,name varchar(10) not null,score decimal(5,2),address varchar(50) default'未知');**
Query OK, 0 rows affected (0.01 sec)
mysql>
1.5.2 显示表内容
mysql> describe info;
---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 未知 | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
给root提权
mysql> grant all privileges on *.* to 'root'@'%' identified by 'asd123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
1.5.3 在表中写入数据
写入数据（字符串要用 单 引号 ‘’ 引起来, 一一对应，数量相同，顺序一致 ）
mysql> insert into info (id,name,score,address) values (1,'zhasan',88.50,'nanjing');
Query OK, 1 row affected (0.01 sec)
1.5.4 查看数据内容
mysql> select * from info;
±—±---------±------±--------+
| id | name | score | address |
±—±---------±------±--------+
| 1 | zhangsan | 88.50 | nanjing |
±—±---------±------±--------+
1 row in set (0.00 sec)
1.5.5 继续添加内容的几种方式
（不跟字段名，默认所有字段，但是需要将数值写全）
mysql> insert into info values (2,‘lisi’,77,‘shanghai’);
Query OK, 1 row affected (0.01 sec)
查看数据
mysql> select * from info;
+----+----------+-------+----------+
| id | name | score | address |
+----+----------+-------+----------+
| 1 | zhangsan | 88.50 | nanjing |
| 2 | lisi | 77.00 | shanghai |
+----+----------+-------+----------+
2 rows in set (0.00 sec)
继续添加内容
mysql> insert into info (name,score,address) values (‘wangwu’,99,‘suzhou’),(‘zhaoliu’,68,default);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select* from info ;
+----+----------+-------+----------+
| id | name | score | address |
+----+----------+-------+----------+
| 1 | zhangsan | 88.50 | nanjing |
| 2 | lisi | 77.00 | shanghai |
| 3 | wangwu | 99.00 | suzhou |
| 4 | zhaoliu | 68.00 | 未知 |
+----+----------+-------+----------+
4 rows in set (0.00 sec)
1.6 筛选出分数大于80 的人
mysql> select * from info where score > 80;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.50 | nanjing |
| 3 | wangwu | 99.00 | suzhou |
+----+----------+-------+---------+
2 rows in set (0.00 sec)
1.6.1 将分数大于80 的 数据内容导出到新表 sha
mysql> create table sha as select * from info where score >80;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info |
| sha |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from sha;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.50 | nanjing |
| 3 | wangwu | 99.00 | suzhou |
+----+----------+-------+---------+
2 rows in set (0.00 sec)
mysql>
1.7 修改，更新数据表中的数据记录的命令
update 表名 set 字段名 1=值1[，字段名2=值2] where条件表达式
设置zhangsan的成绩
mysql> update info set score=55 where name=‘zhangsan’;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
显示表内容
mysql> select * from sha;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.50 | nanjing |
| 3 | wangwu | 99.00 | suzhou |
+----+----------+-------+---------+
2 rows in set (0.00 sec)
mysql> select * from info;
+----+----------+-------+----------+
| id | name | score | address |
+----+----------+-------+----------+
| 1 | zhangsan | 55.00 | nanjing |
| 2 | lisi | 77.00 | shanghai |
| 3 | wangwu | 99.00 | suzhou |
| 4 | zhaoliu | 68.00 | 未知 |
+----+----------+-------+----------+
4 rows in set (0.00 sec)
二 .SQL语言概述及管理
SQL语言
是Structured Query Language的缩写，及结构化查询语言
是关系型数据库的标准语言
用于维护管理数据库，如数据查询，数据更新，访问控制，对象管理等功能
SQL分类
DDL：数据定义语言
DML：数据操纵语言
DQL：数据查询语言
DCL：数据控制语言
SQL操作管理命令
DDL操作命令：
DDL语句是什么作用？
DDL语句用于创建数据库对象，如库，表，索引等
DDL语句创建库，表的命令
创建数据库：create database 数据库名
创建数据表：create table 表名（字段定义…）
. Mysql 用户密码的修改
2.1 创建用户Tom
mysql> GRANT all ON localhost. TO ‘Tom’@’%’ IDENTIFIED BY ‘asd123’;*
Query OK, 0 rows affected, 1 warning (0.00 sec)
2.2 设置跳过密码检查，直接登录进去
[root@shanan ~]# vim /etc/my.cnf
2.3 重启服务，测试无密码登录
[root@shanan ~]# systemctl restart mysqld
[root@shanan ~]# mysql -u root -p
Enter password: // 不用密码 ，回车即可登录
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/orits
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the currentinput statement.
mysql>
mysql> show databases;
+--------------------+
|
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
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 |
。。。。。。。。。
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> select * from user;
2.4 重新设置Tom 的密码
mysql> update mysql.user set authentication_string=password('sha1234567') where user='tom';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 1
mysql> update mysql.user set authentication_string=password('sha1234567') where user='Tom';
2.4.1 刷新数据库
flush privileges;
三. 删除
DDL语句删除库，表的命令
删除指定的数据表：drop table [数据库名.]表名
删除指定的数据库：drop database 数据库名
3.1 在数据表中删除指定的数据记录命令
delete from 表名 where条件表达式
删除成绩大于90 分的
mysql> delete from sha where score >= 90;
Query OK, 1 row affected (0.01 sec)
mysql> select * from sha;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.50 | nanjing |
+----+----------+-------+---------+
1 row in set (0.00 sec)
3.2 删除表 sha 所有数据
不带where条件的语句表示删除表中所有记录（谨慎操作）
mysql> select * from sha;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.50 | nanjing |
+----+----------+-------+---------+
1 row in set (0.00 sec)
3.2.1 删除表数据，添加表数据
DML操作命令
DML语句的作用
DML语句用于对表中的数据进行管理
包括以下操作
insert：插入新数据
update：更新原有数据
delete：删除不需要的数据
向数据表中插入新的数据记录命令是？
insert into 表名（字段1，字段2，…）values（字段1的值，字段2的值，…）
删除表数据
mysql> delete from sha;
Query OK, 1 row affected (0.00 sec)
mysql> select * from sha;
Empty set (0.00 sec)
delete 清空内容，表还在
mysql> truncate table info;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from info;
Empty set (0.00 sec)
Drop 删除，连同表一块删除
mysql> drop table info;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables; // 发现表也没有了
+------------------+
| Tables_in_school |
+------------------+
| sha |
+------------------+
1 row in set (0.00 sec)
mysql> drop tables sha;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
四. 创建临时表：
4.1 创建临时表
mysql> create temporary table temp_info (id int(4) not null auto_increment,name varchar(10) not null,hobby varchar(10) not null,primary key(id))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.00 sec)
4.2 查看临时表
mysql> desc temp_info;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra|
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment|
| name | varchar(10) | NO | | NULL ||
| hobby | varchar(10) | NO | | NULL ||
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
4.3 在 表中写入内容
mysql> insert into temp_info (name,hobby) values('tom','cat');
Query OK, 1 row affected (0.01 sec)
查看内容
mysql> select * from temp_info;
+----+------+-------+
| id | name | hobby |
+----+------+-------+
| 1 | tom | cat |
+----+------+-------+
1 row in set (0.00 sec)
4.4 **查看表
发现查不到创建的临时表，因为表存在内存中，退出就会自动清除
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| sha |
+------------------+
1 row in set (0.00 sec)
mysql>
退出之后查看，发现临时表果然消失了
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| sha |
+------------------+
1 row in set (0.00 sec)
mysql> select * from temp_info;
ERROR 1146 (42S02): Table 'school.temp_info' doesn't exist
创建表 shanan
create table shanan (id int(3) not null primary key auto_increment,name varchar(10) not null,score decimal(5,2),address varchar(50) default'未知');
五. 克隆表
5.1 显示表结构
mysql> desc shanan;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 未知 | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
5.2 克隆表结构shanan
mysql> create table test like shanan;
Query OK, 0 rows affected (0.01 sec)
5.3 查看表列表，发现已生成克隆的 test
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| sha |
| shanan |
| test |
+------------------+
3 rows in set (0.00 sec)
5.4 查看表结构，和克隆的对象完全一致
mysql> desc test;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 未知 | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
5.5 在表中写入内容将表内容克隆到新表
mysql> select * from test;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.00 | nanjing |
+----+----------+-------+---------+
1 row in set (0.00 sec)
将内容克隆到表 由 test 克隆到 shanan
mysql> insert into shanan select * from test;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from shanan;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.00 | nanjing |
+----+----------+-------+---------+
1 row in set (0.00 sec)
六. 用权限的增加与撤销
DCL语句的作用是
设置或查看用户的权限，或者创建用户
设置用户权限的命令是
若用户已存在，则更改用户密码
若用户不存在，则新建用户
GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [IDENTIFIED BY ‘密码′ ]
6.1 查看mysql用户
mysql> select user from mysql.user;
+-----------+
| user |
+-----------+
| Tom |
| root |
| mysql.sys |
| root |
+-----------+
4 rows in set (0.01 sec)
6.2 创建用户jerrry ，并提权
mysql> grant all on *.* to 'jerry'@'localhost' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
刷新
mysql> flush privileges;
6.3查看查看用户权限的命令是？
SHOW GRANTS FOR 用户名@来源地址
当前的grants
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
6.4 查看jerry 的权限
mysql> show grants for ‘jerry’@‘localhost’;
+----------------------------------------------------+
| Grants for jerry@localhost |
+----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'jerry'@'localhost' |
+----------------------------------------------------+
1 row in set (0.00 sec)
6.5 撤销用户权限的命令
REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址
撤销JERRY用户的权限
mysql> revoke all on *.* from 'jerry'@'localhost';
Query OK, 0 rows affected (0.00 sec)
6.6 查看权限已被撤销
mysql> show grants for 'jerry'@'localhost';
+-------------------------------------------+
| Grants for jerry@localhost |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'jerry'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)
6.7 权限操作命令总结
Grand :当用户已存在时，直接提权，当用户不存在时，先创建用户再提权
Revoke : 只撤销权限，不会删除用户
扫一扫
846
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
