Mysql数据库基础
MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一
MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
- MySQL 是开源的，目前隶属于 Oracle 旗下产品。
- MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
- MySQL 使用标准的 SQL 数据语言形式。
- MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
- MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。
- MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
- MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。
数据库安装
见lamp和lnmp篇，里面有详细描述。
基础命令
登录mysql
mysql -uroot -p
[root@localhost ~]# mysql -uroot -p
Enter password: (输入你的数据库密码)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, 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
mysql -uroot -p -h IP地址 -P 端口
[root@localhost ~]#mysql -uroot -p -h 192.168.237.100 -P 2345
Enter password: (输入你的数据库密码)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, 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.
查看当前所有的数据库
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
打开指定的数据库
use 数据库名;
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
查看所有的表
show tables;
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)
显示表的信息
describe/desc 表名;
mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | 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 | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
创建一个数据库
create database 数据库名;
mysql> create database wpc;
Query OK, 1 row affected (0.00 sec)
创建一个数据表
create table 表名(字段1 数据类型 [属性], 字段2 …);
mysql>create table yuangong (姓名 char(10),性别 char(10),年龄 int);
Query OK, 0 rows affected (0.01 sec)
mysql> desc yuangong;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| 性别 | char(10) | YES | | NULL | |
| 年龄 | int(11) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
删除数据库
drop database 数据库名;
mysql>drop database wpc;
删除数据表（必须在库中）
drop table 表名;
mysql> drop table renyuan;
Query OK, 0 rows affected (0.01 sec)
表中插入数据
insert into 表名 (指定字段名称) values(字段的值);
mysql> insert into yuangong (姓名,性别,年龄) values ('吴鹏程','男','26');
Query OK, 1 row affected (0.02 sec)
mysql> select * from yuangong;
+-----------+--------+--------+
| 姓名 | 性别 | 年龄 |
+-----------+--------+--------+
| 吴鹏程 | 男 | 26 |
+-----------+--------+--------+
1 row in set (0.00 sec)
查询表中数据
select 字段1,字段2,… from 表名 where正则表达式;
（*代表任意字段）
mysql> select * from yuangong;
+-----------+--------+--------+
| 姓名 | 性别 | 年龄 |
+-----------+--------+--------+
| 吴鹏程 | 男 | 26 |
+-----------+--------+--------+
1 row in set (0.00 sec)
mysql> select * from yuangong where 年龄=26;
+-----------+--------+--------+
| 姓名 | 性别 | 年龄 |
+-----------+--------+--------+
| 吴鹏程 | 男 | 26 |
+-----------+--------+--------+
1 row in set (0.00 sec)
修改表中数据
update 表名 set 字段=新数据 where正则表达式;
mysql> update yuangong set 年龄=28 where 姓名='吴鹏程';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from yuangong;
+-----------+--------+--------+
| 姓名 | 性别 | 年龄 |
+-----------+--------+--------+
| 吴鹏程 | 男 | 28 |
| 张三 | 男 | 28 |
| 李四 | 男 | 48 |
| 王五 | 男 | 36 |
| 赵六 | 男 | 47 |
+-----------+--------+--------+
5 rows in set (0.00 sec)
删除表中数据
delete from 表名 where正则表达式;
mysql> delete from yuangong where 姓名='吴鹏程';
Query OK, 1 row affected (0.00 sec)
mysql> select * from yuangong;
+--------+--------+--------+
| 姓名 | 性别 | 年龄 |
+--------+--------+--------+
| 张三 | 男 | 28 |
| 李四 | 男 | 48 |
| 王五 | 男 | 36 |
| 赵六 | 男 | 47 |
+--------+--------+--------+
4 rows in set (0.00 sec)
创建表的命令形式显示表结构
show create table 表名\G;
mysql> show create table yuangong\G;
*************************** 1. row ***************************
Table: yuangong
Create Table: CREATE TABLE "yuangong" (
"姓名" char(10) DEFAULT NULL,
"性别" char(10) DEFAULT NULL,
"年龄" int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
模糊查询
select * from yuangong where 字段 like 条件;
mysql> select * from yuangong where 年龄 like '%4%';
+--------+--------+--------+
| 姓名 | 性别 | 年龄 |
+--------+--------+--------+
| 李四 | 男 | 48 |
| 赵六 | 男 | 47 |
+--------+--------+--------+
2 rows in set (0.00 sec)
克隆表
方法1
create table 新表 like 旧表;
insert into 新表 select * from 旧表;
方法2
create table 新表 (select * from 旧表);
方法1
mysql> create table renyuan like yuangong;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into renyuan select * from yuangong;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from renyuan;
+--------+--------+--------+
| 姓名 | 性别 | 年龄 |
+--------+--------+--------+
| 张三 | 男 | 28 |
| 李四 | 男 | 48 |
| 王五 | 男 | 36 |
| 赵六 | 男 | 47 |
+--------+--------+--------+
4 rows in set (0.01 sec)
方法2
mysql> create table yuangong2 (select * from yuangong);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from yuangong2;
+--------+--------+--------+
| 姓名 | 性别 | 年龄 |
+--------+--------+--------+
| 张三 | 男 | 28 |
| 李四 | 男 | 48 |
| 王五 | 男 | 36 |
| 赵六 | 男 | 47 |
+--------+--------+--------+
4 rows in set (0.00 sec)
修改表结构
alter table 表名 RENAME/ADD/CHANGE/DROP 字段名 数据类型 属性;
mysql> alter table yuangong2 rename yuangong3;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_wpc |
+---------------+
| renyuan |
| yuangong |
| yuangong3 |
+---------------+
3 rows in set (0.01 sec)
mysql> alter table yuangong3 change 性别 sex char(10);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from yuangong3;
+--------+------+--------+
| 姓名 | sex | 年龄 |
+--------+------+--------+
| 张三 | 男 | 28 |
| 李四 | 男 | 48 |
| 王五 | 男 | 36 |
| 赵六 | 男 | 47 |
+--------+------+--------+
4 rows in set (0.00 sec)
清空表数据
truncate table 表名;
mysql> truncate table yuangong3;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from yuangong3;
Empty set (0.00 sec)
分页查询limit
select 字段 from 表名 limit 分页数
select 字段 from 表名 limit 分页数开始 查询分页数量
mysql> select * from yuangong limit 2;
+--------+--------+--------+
| 姓名 | 性别 | 年龄 |
+--------+--------+--------+
| 张三 | 男 | 28 |
| 李四 | 男 | 48 |
+--------+--------+--------+
2 rows in set (0.00 sec)
mysql> select * from yuangong limit 2,2;
+--------+--------+--------+
| 姓名 | 性别 | 年龄 |
+--------+--------+--------+
| 王五 | 男 | 36 |
| 赵六 | 男 | 47 |
+--------+--------+--------+
2 rows in set (0.00 sec)
添加mysql数据库用户
create user ‘用户名’@‘地址’ identified by ‘密码’;
mysql> create user zhangsan@localhost identified by '971125';
Query OK, 0 rows affected (0.05 sec)
授权mysql数据库用户
grant 权限列表/all privileges on 库名.* to 用户@‘%’ identified by ‘密码’;
授权wpc库给张三用户所有权限，密码是zhangsan
mysql> grant all privileges on wpc.* to zhangsan@'%' identified by 'zhangsan';
Query OK, 0 rows affected, 1 warning (0.00 sec)
授权所有库给张三用户所有权限，密码是971125
mysql> grant all privileges on *.* to zhangsan@'%' identified by '971125';
Query OK, 0 rows affected, 1 warning (0.00 sec)
更新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
撤销用户权限
revoke 用户权限/all privileges on 库名.* from ‘用户名’@‘地址’;
更改mysql数据库用户名
撤销所有库给张三用户的权限
mysql> revoke all privileges on *.* from zhangsan@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)
更新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
rename user 旧用户名 to 新用户名;
mysql> rename user zhangsan to wpc;
Query OK, 0 rows affected (0.01 sec)
删除mysql数据库用户
drop user 用户名
mysql> drop user wpc;
Query OK, 0 rows affected (0.00 sec)
查看mysql数据库用户相关信息
select 字段1,字段2,字段3 from 用户表;
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root | % | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
| zhangsan | localhost | *65A9B4C6979E0EAC2F2327145428BF0AC64B37EE |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
查看指定用户权限
show grants for 用户名@地址;
mysql> show grants for zhangsan@localhost;
+----------------------------------------------+
| Grants for zhangsan@localhost |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' |
+----------------------------------------------+
1 row in set (0.00 sec)
更改用户密码
set password 用户名@localhost= password(‘新密码’);
mysqladmin -u用户名 -p旧密码 password 新密码
mysql> mysql> set password for root@localhost = password('971125');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysqladmin -uroot -pabc123 password 971125
解决mysql忘记密码
设置mysql跳过密码登录
systemctl stop mysqld
vim /etc/mysql/mysql.conf.d/mysqld.cnf
配置文件中加上
skip-grant-tables
:wq
systemctl restart mysqld
重新修改密码
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> update user set authentication_string=password("abc123") where user="root";
Query OK, 1 row affected, 1 warning (0.05 sec)
Rows matched: 2 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
退出mysql连接
exit
quit
mysql> quit
Bye
mysql> exit
Bye
数据表约束
约束实际上就是表中数据的限制条件,表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效
| 约束条件 | 说明 |
|---|---|
| PRIMARY KEY | 主键约束，用于唯一标识对应的记录，同时保证唯一性和非空 |
| FOREIGN KEY | 外键约束，用于限制两个表的关系，保证从表该字段的值来自于主表相关联的字段的值 |
| NOT NULL | 非空约束，用not null约束的字段不能为null值，必须给定具体的数据 |
| UNIQUE | 唯一约束，unique约束的字段，具有唯一性，不可重复，但可以为null |
| DEFAULT | 默认值约束，保证字段总会有值，即使没有插入值，都会有默认值 |
主键和唯一键的区别
共同点：字段的值都是唯一性，不允许有重复的值
不同点：一个表中 只能有1个主键，但是可以有多个唯一键，主键字段中不允许有null值，唯一键是允许有null
主键约束
创建表时添加表指定主键约束字段
create table 表名 (字段1 XXX,字段2 xxx,…,primary key(字段));
create table 表名 (字段1 XXX primary key, …);
mysql> create table student (id int,name char(10),sex char(10),primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> create table stu (id int primary key,name char(10),sex char(10));
Query OK, 0 rows affected (0.05 sec)
删除表指定主键约束字段
alter table 表名 drop primary key;
mysql> alter table stu drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
添加已有表的主键约束
alter table 表名 ADD primary key(字段);
alter table student add primary key(id);
uery OK, 0 rows affected (0.00 sec)
复合主键约束
创建表时添加联合主键约束
create table 表名 (字段1 XXX,字段2 xxx,…,primary key(字段列表));
mysql> create table stu (id int,name char(10),sex char(10),primary key(id,name));
Query OK, 0 rows affected (0.00 sec)
删除表指定联合主键约束
alter table 数据表名 drop primary key;
mysql> alter table stu drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
添加已有表的联合主键约束
alter table 表名 add primary key(字段列表);
mysql> alter table stu add primary key(id,name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
非空约束
表中添加非空约束
alter table t_表名 modify 字段 属性 not null;
mysql> alter table stu modify name char(20) not null;
uery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
表中删除非空约束
alter table 表名 modify 字段 属性;
mysql> alter table stu modify name char(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
唯一约束
创建表时添加唯一约束
create table 表名 (字段1 属性,字段2 属性 unique,…);
mysql> create table stu (id int,name char(20),sex char(10) unique);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
表中删除唯一约束
alter table 表名 drop index 唯一约束名;
mysql> alter table t_user9 drop index unique_sex;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
表中添加唯一约束
alter table 表名 add constraint 唯一约束名 unique(字段);
mysql> alter table stu add constraint unique_sex unique(sex);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
默认约束
创建表时添加默认约束
create table 表名 (字段1 属性,字段2 属性 default ‘值’,…);
mysql> create table stu (id int,name char(20),sex char(10),address char(10) default '南京');
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除表中默认约束
alter table 表名 modify column 字段 属性 default null;
mysql> alter table stu modify address char(10) default null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
已有表中添加默认约束
alter 表 modify 字段 属性 default ‘值’;
mysql> alter table stu modify address char(10) default '南京';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
外键约束
创建表时添加外键约束
constraint 外键名 foreign key(从表外键字段) references 主表(主键字段)
mysql> create table stu (id int,name char(20),sex char(10),address char(10),constraint fk_class_studentid foreign key(studentid),references stu(id));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
已有表添加外键约束
alter table 从表名 add constraint 外键名 foeign key (从表外键字段) references 主表 (主键字段);
alter table class add constraint fk_class_studentid foreign key(studentid) references stu(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除外键约束
alter table 从表名 drop foreign key 外键名；
alter table class drop foreign key fk_class_studentid;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
扫一扫
3301
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
