文章目录
前言
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一
MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
一:数据库基本操作命令
1.1:MySQL常用管理操作
查看数据库结构
创建及删除库和表
管理表的记录
1.2:查看数据库列表信息的命令
show databases
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
1.3:查看数据库中的数据表信息命令
use 数据库名
show tables
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 |
...省略内容
1.4:显示数据表的结构(字段)命令是
describe [数据库名.]表名
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
...省略内容
1.5:SQL语言概述
SQL语言
是Structured Query Language的缩写,及结构化查询语言
是关系型数据库的标准语言
用于维护管理数据库,如数据查询,数据更新,访问控制,对象管理等功能
SQL分类
DDL:数据定义语言
DML:数据操纵语言
DQL:数据查询语言
DCL:数据控制语言
二:SQL操作管理命令
2.1:DDL操作命令
2.1.1:DDL语句
DDL语句用于创建数据库对象,如库,表,索引等
2.1.2:DDL语句创建库,表的命令
创建数据库:create database 数据库名
创建数据表:create table 表名(字段定义…)
mysql> create database abc; 创建库
Query OK, 1 row affected (0.00 sec)
mysql> use abc;
Database changed
mysql>
mysql> create table bbb( '//创建表'
-> name char(16)not null, '//名称不为空,最多16个字符'
-> passwd char(48) default '', '//密码默认为空,密码最多48个字符'
-> primary key(name)); '//主键'
Query OK, 0 rows affected (0.01 sec)
mysql> create table info (id int(4) not null primary key,name char(10) not null,address varchar(50) default 'nj');
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
...省略内容
2.1.3:DDL语句删除库,表的命令
删除指定的数据表:drop table [数据库名.]表名
删除指定的数据库:drop database 数据库名
mysql> drop table kkk.bbb; '//删除表'
Query OK, 0 rows affected (0.00 sec)
mysql> drop database kkk; '//删除库'
Query OK, 0 rows affected (0.00 sec)
2.2:DML操作命令
2.2.1:DML语句的作用
DML语句用于对表中的数据进行管理
包括以下操作
insert:插入新数据
update:更新原有数据
delete:删除不需要的数据
2.2.2:向数据表中插入新的数据记录命令
insert into 表名(字段1,字段2,…)values(字段1的值,字段2的值,…)
mysql> desc 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)
mysql> insert into info (id,name,score,address) values (1,'zhangsan',88.5,'nanjing');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.50 | nanjing |
+----+----------+-------+---------+
1 row in set (0.00 sec)
mysql> insert into info (id,name,score,address) values (2,'lisi',90,'beijing');
Query OK, 1 row affected (0.00 sec) 添加lisi的信息
mysql> select * from info;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.50 | nanjing |
| 2 | lisi | 90.00 | beijing |
+----+----------+-------+---------+
2 rows in set (0.00 sec)
添加wangwu,zhaoliu的信息
mysql> insert into info (name,score,address) values ('wangwu',70,'suzhou'),('zhaoliu',60,'hangzhou');
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 | 90.00 | beijing |
| 3 | wangwu | 70.00 | suzhou |
| 4 | zhaoliu | 60.00 | hangzhou |
+----+----------+-------+----------+
4 rows in set (0.00 sec)
将成绩80分以上的新建一个tmp表
mysql> create table tmp as select * from info where score >=80;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info |
| tmp |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from tmp;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.50 | nanjing |
| 2 | lisi | 90.00 | beijing |
+----+----------+-------+---------+
2 rows in set (0.00 sec)
修改、更新数据表中的数据记录
update 表名 set 字段名1=值1[,字段名2=值2] where 条件表达式
将表中zhaoliu用户的成绩改成66
mysql> update info set score=6 name='zhaoliu';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from info;
+----+----------+-------+----------+
| id | name | score | address |
+----+----------+-------+----------+
| 1 | zhangsan | 88.50 | nanjing |
| 2 | lisi | 90.00 | beijing |
| 3 | wangwu | 70.00 | suzhou |
| 4 | zhaoliu | 66.00 | hangzhou |
+----+----------+-------+----------+
4 rows in set (0.00 sec)
破解mysql用户密码,进行更改
mysql> grant all privileges on *.* to 'tom'@'localhost' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@localhost ~]# vim /etc/my.cnf
...
skip-grant-tables '添加跳过user表加载'
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# netstat -ntap | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 11593/mysqld
[root@localhost ~]# mysql -uroot -p '此时登录不需要输入密码'
authentication_string '保存密码的字段'
mysql> update mysql.user set authentication_string=password('123456') where user='tom'; '将tom的密码改为123456'
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
[root@localhost ~]# vim /etc/my.cnf
#skip-grant-tables '注释掉'
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# netstat -natp | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 12146/mysqld
[root@localhost ~]# mysql -utom -p
Enter password: '此时输入更改后的密码进入'
Welcome to the MySQL monitor. Commands end with ; or \g.
在数据表中删除指定的数据记录
delete from 表名 where 条件表达式
mysql> select * from tmp;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.50 | nanjing |
| 2 | lisi | 90.00 | beijing |
+----+----------+-------+---------+
2 rows in set (0.01 sec)
mysql> delete from tmp where score >=90; 删除所有高于90分的数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from tmp;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | zhangsan | 88.50 | nanjing |
+----+----------+-------+---------+
1 row in set (0.00 sec)
不带where条件的语句表示删除表中所有记录
mysql> delete from tmp;
Query OK, 1 row affected (0.00 sec)
mysql> select * from tmp;
Empty set (0.00 sec)
DQL是数据查询语句,只有SELECT
用于从数据表中查找符合条件的数据记录
查询时可不指定条件
select 字段名1,字段名2.....from 表名
mysql> select * from tmp;
+----+----------+-------+----------+
| id | name | score | address |
+----+----------+-------+----------+
| 1 | zhangsan | 80.00 | shanghai |
| 2 | lisi | 90.00 | beijing |
+----+----------+-------+----------+
2 rows in set (0.00 sec)
查询时指定条件
mysql> select name,score from tmp where name='zhangsan';
+----------+-------+
| name | score |
+----------+-------+
| zhangsan | 80.00 |
+----------+-------+
1 row in set (0.00 sec)
2.3 数据表高级操作
清空表
delete from tablename
truncate table tablename
mysql> truncate table tmp;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tmp;
Empty set (0.00 sec)
临时表
create temporary table 表名 ((字段01名称字段01类型字段01约束,字段02名字段02类型字段02约束,......)存储引擎,字符集
mysql> create temporary table mytmp(id int(3) not null auto_increment,name varchar(10) not null,hobby varchar(10) not null,primary key(id))engine=innodb default charset=utf8;
mysql> desc mytmp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| hobby | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
克隆表
like方法
临时建立的表,用于保存一些临时数据, 不会长期存在(连接断开,临时表被删除)
create table test like mytmp; '从mytmp完整复制结构生成test表,再导入数据'
insert into test select * from mytmp; '先查看mytmp表完整结构,根据此结构创建名字不同结构相同的表test,再导入数据'
mysql> insert into mytmp values (1,'zhangsan','read'),(2,'lisi','computer'),(3,'wangwu','basketball');
mysql> select * from mytmp;
+----+----------+------------+
| id | name | hobby |
+----+----------+------------+
| 1 | zhangsan | read |
| 2 | lisi | computer |
| 3 | wangwu | basketball |
+----+----------+------------+
3 rows in set (0.00 sec)
mysql> create table test like mytmp;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test select * from mytmp;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+----------+------------+
| id | name | hobby |
+----+----------+------------+
| 1 | zhangsan | read |
| 2 | lisi | computer |
| 3 | wangwu | basketball |
+----+----------+------------+
3 rows in set (0.00 sec)
show create table 方法
show create table mytmp\G; '先查看mytmp表完整结构,根据此结构创建名字不同结构相同的表test,再导入数据'
create table test(....);
insert into test select * from mytmp;
DCL语句设置用户权限(用户不存在时,则新建用户)
grant 权限列表 on 数据库名.表名 to 用户名@来源地址 [identified by '密码']
mysql> grant all on *.* to 'zhaoliu'@'localhost' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges; '刷新'
Query OK, 0 rows affected (0.00 sec)
查看用户的权限
show grants for 用户名@来源地址
mysql> show grants for 'zhaoliu'@'localhost';
+------------------------------------------------------+
| Grants for zhaoliu@localhost |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhaoliu'@'localhost' |
+------------------------------------------------------+
1 row in set (0.00 sec)
撤销用户的权限
revoke 权限列表 on 数据库名.表名 from 用户名@来源地址
mysql> revoke all on *.* from 'zhaoliu'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'zhaoliu'@'localhost';
+---------------------------------------------+
| Grants for zhaoliu@localhost |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'zhaoliu'@'localhost' |
+---------------------------------------------+
1 row in set (0.00 sec)
三、Windows字符界面安装MySQL
1.准备安装包
2.开始安装
已经安装好mysql了
2、添加环境变量
3.运行mysql数据库