【大部分SQL操作命令必须以;结束】
mysql> show databases; 【查看当前数据库里有哪些库】
mysql> use mysql; 【进入mysql数据库】
mysql> show tables; 【查看当前数据库中的表信息】
mysql> describe mysql.user; 【显示数据表的结构(字段)】
mysql> describe user;
mysql> desc user; 【也可以使用desc user;】
类型 | 说明 |
---|---|
int | 整型 用于定义整数类型的数据 |
float | 单精度浮点4字节32位 准确表示到小数点后六位 |
double | 双精度浮点8字节64位 |
char | 固定长度的字符类型 用于定义字符类型数据 如果存入数据的实际长度比指定长度要小,则会补空格至指定长度 如果存入数据的实际长度大于指定长度,低版本会被截取,高版本会报错 |
varchar | 可变长度的字符类型 |
text | 文本 |
image | 图片 |
decimal(5,3) | 5个有效长度数字,小数点后面有3位 |
mysql> create database qz1; 【创建数据库qz1】
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| mysql |
| performance_schema |
| qz1 |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use qz1 【进入qz1数据库】
mysql> create table zq (id int not null,name char(8) not null,score decimal(6,3),passwd char(80));
Query OK, 0 rows affected (0.07 sec)
【创建表,名为zq(id为整型且不能为空值,name为固定8个长度的字符,score为6个有效长度的数字,且小数点后面有3位),passwd固定80个长度字符】
mysql> desc zq; 【显示zq数据表的结构(字段)】
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(8) | NO | | NULL | |
| score | decimal(6,3) | YES | | NULL | |
| passwd | char(80) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.05 sec)
【default的默认值为空】
【primary key(主键名):主键一般选择能代表唯一性的字段且不允许为空值(null),一个表只能由一个主键】
【主键是唯一的,但可以有多个字段构成】
【Extre:步长】
mysql> create table zqusers (user_name char(8) not null,user_passwd char(80),primary key (user_name));
Query OK, 0 rows affected (0.06 sec)
mysql> desc zqusers; 【显示zqusers数据表的结构(字段)】
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| user_name | char(8) | NO | PRI | NULL | |
| user_passwd | char(80) | YES | | NULL | |
+-------------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> create table 皇家娱乐 (姓名 int not null,性别 char(8),身高 char(4),工号 char(6),primary key (工号));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_qz1 |
+---------------+
| 皇家娱乐 |
| zq |
| zqusers |
+---------------+
4 rows in set (0.00 sec)
mysql> create table 天上人间 (姓名 char(6) not null,性别 char(8),身高 char(4),工号 char(6),primary key (工号));
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+---------------+
| Tables_in_qz1 |
+---------------+
| 天上人间 |
| 皇家娱乐 |
| zq |
| zqusers |
+---------------+
5 rows in set (0.00 sec)
【删除表名为zq的表】
mysql> use qz1 【进入qz1库】
mysql> show tables;
+---------------+
| Tables_in_qz1 |
+---------------+
| zq |
| zqusers |
+---------------+
2 rows in set (0.00 sec)
mysql> drop table zq;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_qz1 |
+---------------+
| zqusers |
+---------------+
1 row in set (0.00 sec)
【删除指定的数据库】
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| mysql |
| performance_schema |
| qz1 |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database qz1;
Query OK, 1 row affected (0.06 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
【删除主键】
mysql> desc 皇家娱乐;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| 姓名 | int(11) | NO | | NULL | |
| 性别 | char(8) | YES | | NULL | |
| 身高 | char(4) | YES | | NULL | |
| 工号 | char(6) | NO | PRI | NULL | |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table 皇家娱乐 drop primary key;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc 皇家娱乐;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| 姓名 | int(11) | NO | | NULL | |
| 性别 | char(8) | YES | | NULL | |
| 身高 | char(4) | YES | | NULL | |
| 工号 | char(6) | NO | | NULL | |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
【删除指定表的指定字段】
mysql> alter table 天上人间 drop 身高;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc 天上人间;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| 姓名 | char(6) | NO | | NULL | |
| 性别 | char(8) | YES | | NULL | |
| 编号 | varchar(6) | NO | PRI | NULL | |
| 家庭地址 | varchar(60) | NO | | 未知 | |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_qz1 |
+---------------+
| 天上人间 |
| 皇家娱乐 |
| q |
| zq |
| zqusers |
+---------------+
5 rows in set (0.00 sec)
mysql> alter table q rename 皇家一号;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_qz1 |
+---------------+
| 天上人间 |
| 皇家一号 |
| 皇家娱乐 |
| zq |
| zqusers |
+---------------+
mysql> alter table 天上人间 add 家庭地址 varchar(60) not null default '未知';
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc 天上人间;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| 姓名 | char(6) | NO | | NULL | |
| 性别 | char(8) | YES | | NULL | |
| 身高 | char(4) | YES | | NULL | |
| 工号 | char(6) | NO | PRI | NULL | |
| 家庭地址 | varchar(60) | NO | | 未知 | |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table 天上人间 change 工号 编号 varchar(6) unique key;
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc 天上人间;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| 姓名 | char(6) | NO | | NULL | |
| 性别 | char(8) | YES | | NULL | |
| 身高 | char(4) | YES | | NULL | |
| 编号 | varchar(6) | NO | PRI | NULL | |
| 家庭地址 | varchar(60) | NO | | 未知 | |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
【先创建数据库qz1,再在qz1数据库里创建zqusers表格】
mysql> create database qz1;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| mysql |
| performance_schema |
| qz1 |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use qz1
Database changed
mysql> create table zqusers (user_name char(8) not null,user_passwd char(80),primary key (user_name));
mysql> show tables;
+---------------+
| Tables_in_qz1 |
+---------------+
| zqusers |
+---------------+
1 row in set (0.00 sec)
mysql> desc zqusers;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| user_name | char(8) | NO | PRI | NULL | |
| user_passwd | char(80) | YES | | NULL | |
+-------------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
【在zqusers表格里的user_name和user_passwd字段分别插入‘思思’和‘111222’】
【加PASSWORD后密码字串将会以加密后的形式显示,不加则直接显示】
【需注意如以密文显示时需再之前创建表格时的固定长度给大,否则密文超过设置的固定长度则插入失败】
mysql> insert into zqusers (user_name,user_passwd) values('思思',PASSWORD('111222'));
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from zqusers;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| 思思 | *890406EBC2A6D76621F6015C608E2B270BC92D22 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into zqusers (user_name,user_passwd) values('悦悦','111222');
Query OK, 1 row affected (0.05 sec)
mysql> select * from zqusers;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| 思思 | *890406EBC2A6D76621F6015C608E2B270BC92D22 |
| 悦悦 | 111222 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> insert into zqusers values('橙橙','2233') ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from zqusers;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| 思思 | *890406EBC2A6D76621F6015C608E2B270BC92D22 |
| 悦悦 | 111222 |
| 橙橙 | 2233 |
+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> insert into 天上人间 (姓名,性别,身高,工号) values ('悦悦','女',165,013);
Query OK, 1 row affected (0.05 sec)
mysql> insert into 天上人间 (姓名,性别,身高,工号) values ('橙橙','女',160,014);
Query OK, 1 row affected (0.00 sec)
mysql> select * from 天上人间;
+--------+--------+--------+--------+
| 姓名 | 性别 | 身高 | 工号 |
+--------+--------+--------+--------+
| 丹丹 | 女 | 171 | 12 |
| 悦悦 | 女 | 165 | 13 |
| 橙橙 | 女 | 160 | 14 |
+--------+--------+--------+--------+
3 rows in set (0.00 sec)
【where只能对设为primary key主键的字段使用。所以这里只能对user_name字段使用】
【修改、更新user_passwd=5514给字段名user_name=悦悦的字段】
mysql> update zqusers set user_passwd=('5514') where user_name='悦悦';
Query OK, 0 rows affected (0.05 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from zqusers;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| 思思 | *890406EBC2A6D76621F6015C608E2B270BC92D22 |
| 悦悦 | 5514 |
| 橙橙 | 2233 |
+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> select * from zqusers;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| 丹丹 | 2233 |
| 乐乐 | 2233 |
| 思思 | *890406EBC2A6D76621F6015C608E2B270BC92D22 |
| 悦悦 | 5514 |
| 橙橙 | 2233 |
+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
【将zausers表里的满足user_name=橙橙条件的行删除】
mysql> delete from zqusers where user_name='橙橙';
Query OK, 1 row affected (0.06 sec)
mysql> select * from zqusers;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| 丹丹 | 2233 |
| 乐乐 | 2233 |
| 思思 | *890406EBC2A6D76621F6015C608E2B270BC92D22 |
| 悦悦 | 5514 |
+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
【查询天上人间表格里的数据记录】
mysql> select * from 天上人间;
+--------+--------+--------+--------+
| 姓名 | 性别 | 身高 | 工号 |
+--------+--------+--------+--------+
| 丹丹 | 女 | 171 | 12 |
| 悦悦 | 女 | 165 | 13 |
| 橙橙 | 女 | 160 | 14 |
+--------+--------+--------+--------+
3 rows in set (0.00 sec)
【查询天上人间表格里的前3行】
mysql> select * from 天上人间 info limit 3;
+--------+--------+--------+--------+
| 姓名 | 性别 | 身高 | 工号 |
+--------+--------+--------+--------+
| 丹丹 | 女 | 171 | 12 |
| 悦悦 | 女 | 165 | 13 |
| 橙橙 | 女 | 160 | 14 |
+--------+--------+--------+--------+
3 rows in set (0.00 sec)
【查询天上人间表格里姓名一列,并以列表方式竖向显示。这里不用加";"】
mysql> select 姓名 from 天上人间\G
*************************** 1. row ***************************
姓名: 丹丹
*************************** 2. row ***************************
姓名: 悦悦
*************************** 3. row ***************************
姓名: 橙橙
*************************** 4. row ***************************
姓名: 思思
4 rows in set (0.00 sec)
【在天上人间表格里查询满足条件(身高=165)的姓名和工号列】
mysql> select 姓名,工号 from 天上人间 where 身高='165';
+--------+--------+
| 姓名 | 工号 |
+--------+--------+
| 悦悦 | 13 |
| 思思 | 15 |
+--------+--------+
2 rows in set (0.00 sec)
【查询天上人间表格里的第二行数据后面的2行数据】
mysql> select * from 天上人间 info limit 2,2;
+--------+--------+--------+--------+
| 姓名 | 性别 | 身高 | 工号 |
+--------+--------+--------+--------+
| 橙橙 | 女 | 160 | 14 |
| 思思 | 女 | 165 | 15 |
+--------+--------+--------+--------+
2 rows in set (0.00 sec)
mysql> create table if not exists 皇家二号 (工号 int(6) zerofill primary key auto_increment,绰号 varchar(8) not null,手机号 int(11) not null unique key,特长 varchar(100));
mysql> desc 皇家二号;
+-----------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------------------+------+-----+---------+----------------+
| 工号 | int(6) unsigned zerofill | NO | PRI | NULL | auto_increment |
| 绰号 | varchar(8) | NO | | NULL | |
| 手机号 | int(11) | NO | UNI | NULL | |
| 特长 | varchar(100) | YES | | NULL | |
+-----------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> create table 皇家三号 like 天上人间;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from 天上人间;
+--------+--------+--------+--------------+
| 姓名 | 性别 | 编号 | 家庭地址 |
+--------+--------+--------+--------------+
| 丹丹 | 女 | 12 | 未知 |
| 悦悦 | 女 | 13 | 未知 |
| 橙橙 | 女 | 14 | 未知 |
| 思思 | 女 | 15 | 未知 |
+--------+--------+--------+--------------+
4 rows in set (0.00 sec)
mysql> select * from 皇家三号;
Empty set (0.00 sec)
【复制、备份内容】
mysql> insert into 皇家三号 select * from 天上人间;
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from 皇家三号;
+--------+--------+--------+--------------+
| 姓名 | 性别 | 编号 | 家庭地址 |
+--------+--------+--------+--------------+
| 丹丹 | 女 | 12 | 未知 |
| 悦悦 | 女 | 13 | 未知 |
| 橙橙 | 女 | 14 | 未知 |
| 思思 | 女 | 15 | 未知 |
+--------+--------+--------+--------------+
4 rows in set (0.00 sec)
mysql> create table 皇家四号 (select * from 天上人间);
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from 皇家四号;
+--------+--------+--------+--------------+
| 姓名 | 性别 | 编号 | 家庭地址 |
+--------+--------+--------+--------------+
| 丹丹 | 女 | 12 | 未知 |
| 悦悦 | 女 | 13 | 未知 |
| 橙橙 | 女 | 14 | 未知 |
| 思思 | 女 | 15 | 未知 |
+--------+--------+--------+--------------+
4 rows in set (0.00 sec)
mysql> select * from 天上人间;
+--------+--------+--------+--------------+
| 姓名 | 性别 | 编号 | 家庭地址 |
+--------+--------+--------+--------------+
| 丹丹 | 女 | 12 | 未知 |
| 悦悦 | 女 | 13 | 未知 |
| 橙橙 | 女 | 14 | 未知 |
| 思思 | 女 | 15 | 未知 |
+--------+--------+--------+--------------+
4 rows in set (0.00 sec)
【获取数据表的表结构、索引等信息】
mysql> show create table 皇家四号\G
*************************** 1. row ***************************
Table: 皇家四号
Create Table: CREATE TABLE "皇家四号" (
"姓名" char(6) NOT NULL,
"性别" char(8) DEFAULT NULL,
"编号" varchar(6) NOT NULL,
"家庭地址" varchar(60) NOT NULL DEFAULT '未知'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table 天上人间\G
*************************** 1. row ***************************
Table: 天上人间
Create Table: CREATE TABLE "天上人间" (
"姓名" char(6) NOT NULL,
"性别" char(8) DEFAULT NULL,
"编号" varchar(6) NOT NULL,
"家庭地址" varchar(60) NOT NULL DEFAULT '未知',
UNIQUE KEY "编号" ("编号")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> delete from 皇家四号;
Query OK, 4 rows affected (0.05 sec)
mysql> select * from 皇家四号;
Empty set (0.00 sec)
mysql> select * from 皇家三号;
+--------+--------+--------+--------------+
| 姓名 | 性别 | 编号 | 家庭地址 |
+--------+--------+--------+--------------+
| 丹丹 | 女 | 12 | 未知 |
| 悦悦 | 女 | 13 | 未知 |
| 橙橙 | 女 | 14 | 未知 |
| 思思 | 女 | 15 | 未知 |
+--------+--------+--------+--------------+
4 rows in set (0.00 sec)
mysql> truncate table 皇家三号;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from 皇家三号;
Empty set (0.00 sec)
mysql> create temporary table 皇家临时工 (工号 int(6) zerofill primary key auto_increment,姓名 varchar(8) not null,手机号 int(11) not null unique key,特长 varchar(200));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into 皇家临时工 values (1,'晨晨',331221382,'tea');
Query OK, 1 row affected (0.00 sec)
mysql> select *from 皇家临时工;
+--------+--------+-----------+--------+
| 工号 | 姓名 | 手机号 | 特长 |
+--------+--------+-----------+--------+
| 000001 | 晨晨 | 331221382 | tea |
+--------+--------+-----------+--------+
1 row in set (0.00 sec)
【创建主表皇家9号】
mysql> create table 皇家9号 (爱好 int(50),地址 varchar(80));
Query OK, 0 rows affected (0.07 sec)
【创建从表皇家10号】
mysql> create table 皇家10号 (工号 int(5) primary key auto_increment,姓名 varchar(10),年龄 int(3),地址 varchar(80));
Query OK, 0 rows affected (0.06 sec)
mysql> desc 皇家9号;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| 爱好 | int(50) | YES | | NULL | |
| 地址 | varchar(80) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc 皇家10号;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| 工号 | int(5) | NO | PRI | NULL | auto_increment |
| 姓名 | varchar(10) | YES | | NULL | |
| 年龄 | int(3) | YES | | NULL | |
| 地址 | varchar(80) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
【为主表皇家9号添加一个主键约束。主键名建议以“PK”开头,因为“主键约束(primary key)”】
mysql> alter table 皇家9号 add constraint PK_地址 primary key (地址);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc 皇家9号;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| 爱好 | int(50) | YES | | NULL | |
| 地址 | varchar(80) | NO | PRI | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
【为从表皇家10号添加外键,并将皇家10号表的地址字段和皇家9号的地址字段建立外键关联。】
【外键建议以“FK_开头”,因为“外键约束(foreign key)”】
mysql> alter table 皇家10号 add constraint FK_地址 foreign key (地址) references 皇家9号 (地址);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc 皇家10号;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| 工号 | int(5) | NO | PRI | NULL | auto_increment |
| 姓名 | varchar(10) | YES | | NULL | |
| 年龄 | int(3) | YES | | NULL | |
| 地址 | varchar(80) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
【可以使用查询语句结构命令来查看外键关联】
mysql> show create table 皇家10号;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 皇家10号 | CREATE TABLE "皇家10号" (
"工号" int(5) NOT NULL AUTO_INCREMENT,
"姓名" varchar(10) DEFAULT NULL,
"年龄" int(3) DEFAULT NULL,
"地址" varchar(80) DEFAULT NULL,
PRIMARY KEY ("工号"),
KEY "FK_地址" ("地址"),
CONSTRAINT "FK_地址" FOREIGN KEY ("地址") REFERENCES "皇家9号" ("地址")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
【插入新数据记录时,要先主表再从表,且建立外键的字段待插入的内容需一致】
mysql> insert into 皇家9号 values(1,'nanjing');
Query OK, 1 row affected (0.06 sec)
mysql> insert into 皇家10号 values(11,'悦悦',21,'nanjing');
Query OK, 1 row affected (0.00 sec)
mysql> select * from 皇家9号;
+--------+---------+
| 爱好 | 地址 |
+--------+---------+
| 1 | nanjing |
+--------+---------+
1 row in set (0.00 sec)
mysql> select * from 皇家10号;
+--------+--------+--------+---------+
| 工号 | 姓名 | 年龄 | 地址 |
+--------+--------+--------+---------+
| 11 | 悦悦 | 21 | nanjing |
+--------+--------+--------+---------+
1 row in set (0.00 sec)
【如果要删除外键约束字段,应先删除外键约束,再对外键名进行删除】
mysql> alter table 皇家10号 drop foreign key FK_地址;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table 皇家10号 drop key FK_地址;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table 皇家10号;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 皇家10号 | CREATE TABLE "皇家10号" (
"工号" int(5) NOT NULL AUTO_INCREMENT,
"姓名" varchar(10) DEFAULT NULL,
"年龄" int(3) DEFAULT NULL,
"地址" varchar(80) DEFAULT NULL,
PRIMARY KEY ("工号")
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
【删除数据记录时,要先删从表再删主表,也就是说删除主键表时必须先删除其他与之关联的表】
mysql> drop tables 皇家10号;
Query OK, 0 rows affected (0.01 sec)
mysql> drop tables 皇家9号;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_qz1 |
+---------------+
| 天上人间 |
| 皇家7号 |
| 皇家8号 |
| 皇家一号 |
| 皇家三号 |
| 皇家二号 |
| 皇家五号 |
| 皇家六号 |
| 皇家四号 |
| 皇家娱乐 |
| zq |
| zqusers |
+---------------+
12 rows in set (0.00 sec)
mysql> create user 'qz2'@'localhost' identified by '5514';
Query OK, 0 rows affected (0.01 sec)
mysql> select password('qz5514');
+-------------------------------------------+
| password('qz5514') |
+-------------------------------------------+
| *EEF6AB691B0DC942DA41D4E080CC33B721FE4532 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> create user 'qz3'@'localhost' identified by password '*EEF6AB691B0DC942DA41D4E080CC33B721FE4532';
Query OK, 0 rows affected, 1 warning (0.00 sec)
【创建后的用户都保存在mysql数据库的user表里】
mysql> use mysql;
Database changed
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| bbsuser | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| qz2 | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | localhos |
| qz3 | *EEF6AB691B0DC942DA41D4E080CC33B721FE4532 | localhos |
+---------------+-------------------------------------------+-----------+
7 rows in set (0.00 sec)
mysql> rename user 'qz4'@'localhost' to 'qz5'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| bbsuser | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| qz2 | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | localhos |
| qz3 | *EEF6AB691B0DC942DA41D4E080CC33B721FE4532 | localhos |
| qz5 | *EEF6AB691B0DC942DA41D4E080CC33B721FE4532 | localhost |
+---------------+-------------------------------------------+-----------+
8 rows in set (0.00 sec)
mysql> drop user 'qz3'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| bbsuser | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| qz2 | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | localhos |
| qz5 | *EEF6AB691B0DC942DA41D4E080CC33B721FE4532 | localhost |
+---------------+-------------------------------------------+-----------+
7 rows in set (0.00 sec)
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| bbsuser | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| qz2 | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | localhos |
| qz5 | *EEF6AB691B0DC942DA41D4E080CC33B721FE4532 | localhost |
+---------------+-------------------------------------------+-----------+
7 rows in set (0.00 sec)
【不指定用户时默认为当前用户,这里修改的就是root用户密码】
mysql> set password = password ('qwe');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *BBAE266E0E1E92B3A857E20260D41B7BC259297F | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| bbsuser | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| qz2 | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | localhos |
| qz5 | *EEF6AB691B0DC942DA41D4E080CC33B721FE4532 | localhost |
+---------------+-------------------------------------------+-----------+
7 rows in set (0.00 sec)
【修改其他用户密码】
mysql> set password for 'qz5'@'localhost' = password('qwe');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *BBAE266E0E1E92B3A857E20260D41B7BC259297F | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| bbsuser | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | % |
| qz2 | *A5D0A2506C6B8BCB7D8D7BBA6E928CB63D8F68FE | localhos |
| qz5 | *BBAE266E0E1E92B3A857E20260D41B7BC259297F | localhost |
+---------------+-------------------------------------------+-----------+
7 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost /]# mysql -uqz5 -pqwe;
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.20 Source distribution
【忘记root密码的解决方法】
[root@localhost /]# vim /etc/my.cnf 【编辑配置文件,在mysqld里面添加一项】
29 skip-grant-tables
[root@localhost /]# systemctl restart mysqld.service
[root@localhost /]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
【使用SQL语句修改root密码】
mysql> update mysql.user set authentication_string = password('5514') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 1
mysql> flush privileges; 【刷新权限相关的表,即当对权限进行了相关的操作后使用】
Query OK, 0 rows affected (0.00 sec)
[root@localhost /]# vim /etc/my.cnf 【删除skip-grant-tables并重启服务】
[root@localhost /]# systemctl restart mysqld.service
[root@localhost /]# mysql -u root -p5514
mysql: [Warning] Using a password on the command line interface can be insecure.
【只允许用户qz5在本地查询qz1数据库中所有表的数据记录 】
mysql> grant select on qz1.* to 'qz5'@'localhost' identified by 'qwe';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> exit
Bye
[root@localhost /]# mysql -u qz5 -pqwe
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| qz1 |
+--------------------+
2 rows in set (0.00 sec)
mysql> use qz1
mysql> show tables;
+---------------+
| Tables_in_qz1 |
+---------------+
| 天上人间 |
| 皇家10号 |
...略...
| 皇家娱乐 |
| zq |
| zqusers |
+---------------+
14 rows in set (0.00 sec)
mysql> select * from 天上人间;
+--------+--------+--------+--------------+
| 姓名 | 性别 | 编号 | 家庭地址 |
+--------+--------+--------+--------------+
| 丹丹 | 女 | 12 | 未知 |
| 悦悦 | 女 | 13 | 未知 |
| 橙橙 | 女 | 14 | 未知 |
| 思思 | 女 | 15 | 未知 |
+--------+--------+--------+--------------+
4 rows in set (0.00 sec)
mysql> drop table zq;
ERROR 1142 (42000): DROP command denied to user 'qz5'@'localhost' for table 'zq'
【拒绝DROP删除命令】
【允许用户qz5在所有终端连接mysql,并拥有所有权限】
mysql> grant all privileges on *.* to 'qz5'@'%' identified by '5514';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges; 【刷新权限】
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'qz5'@'%'; 【查看qz5权限,GRANT ALL PRIVILEGES:所有权限】
+------------------------------------------+
| Grants for qz5@% |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'qz5'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> revoke all privileges on *.* FROM 'qz5'@'%';
Query OK, 0 rows affected (0.01 sec)
【查看权限】
mysql> show grants for 'qz5'@'%';
+---------------------------------+
| Grants for qz5@% |
+---------------------------------+
| GRANT USAGE ON *.* TO 'qz5'@'%' |
+---------------------------------+
1 row in set (0.00 sec)
安装和更新驱动是PC老鸟们最熟悉不过的事情了,但在近几年尤其是Windows 10发布...
先看看效果图: 首先给大家解释一些符号相关的意义 * 匹配前面的子表达式零次或...
面试中我们经常会碰到的关于分库分表的问题!今天就给大家介绍互联网公司常用 MyS...
我们在实际工作中,有很多分页的需求,商品分页、订单分页等,在MySQL中我们可以...
一、WEB上支持的图片格式: GIF:能保存256中颜色,支持透明色,支持动画效果 JP...
前言 Flink Table 和 SQL 内置了很多 SQL 中支持的函数;如果有无法满足的需要,...
过去几个月,线上会议已经成为了许多人工作生活中不可或缺的一环。在正式的场合...
类通过5种特殊的成员函数 拷贝构造函数(copy constructor)、拷贝赋值运算符(copy...
本文实例分析了JSP页面文件中base标记用法。分享给大家供大家参考,具体如下: ...
紧接着上篇— 分页技术原理与实现之Java+Oracle代码实现分页(二) ,本篇继续分析...