MySQL基础进阶

MySQL常用命令：(在MySQL的cmd下)

SELECT VERSION();：显示当前服务器版本

SELECT NOW();：显示当前日期时间

SELECT USER();：显示当前用户

show global variables like 'port';：查看MySQL端口号

MySQL语句的规范：

关键字和函数名称全部大写

数据库、表、字段名称全部小写

SQL语句必须以分号结尾

MySQL的CURD
增加：insert into 数据表(字段1,字段2,字段3) values('值1','值2','值3')
删除：delete from 数据表 where id=你要删除的数据id
　　注：MySQL客户端清除表数据　　TRUNCATE 表名
修改：update 数据表 set 字段1=‘值1’,字段2=‘值2’,字段3=‘值3’ where id=你要修改的数据ID
查询：select（你要查询的字段，*代表全部字段） from 表名 where （你的查询条件）

队列排序查询表单数据：

SELECT * FROM local_episode \G;

关联表多表查询：

select a.book_id,a.student_id,a.appoint_time,b.id "book.id",b.name "book.name",b.number "book.number" 
from appointment a 
inner join book b on a.book_id = b.id
where a.book_id = #{bookId,jdbcType=BIGINT}
and a.student_id = #{studentId,jdbcType=BIGINT}

查询单表中重复字段：

SELECT * FROM user3 WHERE user_name IN (SELECT user_name FROM user3 GROUP BY user_name HAVING count(user_name) > 1);

查询两个表的交集：

mysql> SELECT * FROM user1 a,user2 b WHERE a.user_name=b.user_name;
mysql> SELECT * FROM user1 a INNER JOIN user2 b ON a.user_name=b.user_name;

更新两表中重复记录在A表中的字段值：

mysql> UPDATE user2 SET user_name='李查查' WHERE number IN (SELECT b.number FROM user1 a INNER JOIN user2 b ON a.number=b.number);

　　这样写mysql是不能使用的,不能先select出同一表中的某些值，再update这个表(在同一语句中)

UPDATE user2 SET user_name='李查查' WHERE number IN (SELECT number FROM(SELECT b.number FROM user1 a ,user2 b WHERE a.number=b.number) user2);

UPDATE user2 SET user_name='李查查' WHERE number IN (SELECT number FROM(SELECT b.number FROM user1 a INNER JOIN user2 b ON a.number=b.number) user2);

用JOIN解决问题

UPDATE user2 b JOIN (SELECT b.number FROM user1 a INNER JOIN user2 b ON a.number=b.number) a ON a.number=b.number SET b.user_name='李查查';

使用JOIN优化子查询：

查询两表交集在某个表中的字段值：

　　mysql> SELECT b.user_name FROM user1 a,user2 b WHERE a.number=b.number;

查询a表所有字段值及两表交集在b表中的某个字段值：

　　mysql> SELECT a.user_name,a.number,(SELECT user_name FROM user2 b WHERE a.number=b.number) AS user_name2 FROM user1 a;

上述子查询比较低效，可以使用JOIN进行优化：

SELECT a.user_name,a.number,b.user_name AS user_name2 FROM user1 a LEFT JOIN user2 b ON a.number=b.number;

使用JOIN优化聚合子查询：
查询user3对应user1表中人员的最大年龄：

SELECT MAX(a.age) FROM user3 a,user1 b WHERE a.user_name=b.user_name;

查询user3对应user1表中人员的最大年龄的班级号：

SELECT a.user_name,b.age,b.classes FROM user1 a JOIN user3 b ON a.user_name=b.user_name WHERE b.age=(SELECT MAX(c.age) FROM user3 c,user1 d WHERE c.user_name=d.user_name);
或者
SELECT b.user_name,b.age,b.classes FROM user3 b WHERE b.age=(SELECT MAX(c.age) FROM user3 c,user1 d WHERE c.user_name=d.user_name);

仅使用MAX，得不到上面的结果集：

SELECT a.user_name,MAX(b.age),b.classes FROM user1 a JOIN user3 b ON a.user_name=b.user_name GROUP BY a.user_name,b.classes;

如何实现分组选择：

1 查询2个关联表交集中人员年龄最大的前两个数据

SELECT a.user_name,b.age,b.classes FROM user1 a JOIN user3 b ON a.user_name=b.user_name WHERE a.user_name='张三' ORDER BY b.age DESC LIMIT 2;
SELECT a.user_name,b.age,b.classes FROM user1 a JOIN user3 b ON a.user_name=b.user_name WHERE a.user_name='李四' ORDER BY b.age DESC LIMIT 2;
SELECT a.user_name,b.age,b.classes FROM user1 a JOIN user3 b ON a.user_name=b.user_name WHERE a.user_name='...' ORDER BY b.age DESC LIMIT 2;

2 优化查询以上几条数据：

SELECT user_name,age,classes,(SELECT COUNT(*) FROM user3 b WHERE b.user_name=a.user_name AND a.age<=b.age) AS cnt FROM user3 a GROUP BY user_name,age,classes;

SELECT d.user_name,c.age,c.classes FROM (SELECT user_name,age,classes,(SELECT COUNT(*) FROM user3 b WHERE b.user_name=a.user_name AND a.age<=b.age) AS cnt FROM user3 a GROUP BY user_name,age,classes) c JOIN user1 d ON c.user_name=d.user_name WHERE cnt <= 2;

关系型数据库就是一张二维表格，由行（记录）和列（字段）组成。

查看当前MySQL中所有的数据库列表：SHOW DATABASES;

创建数据库：CREATE DATABASE user;

　　CREATE DATABASE IF NOT EXISTS user;(数据库user已存在情况下)

先打开数据库：USE user;

查看当前被打开的数据库：SELECT DATABASE();

查看当前数据库的所有表：SHOW TABLES;

查看指定数据库的数据表：SHOW TABLES FROM mobile_scm;

显示错误信息：SHOW WARNINGS;

查看数据库创建时使用的指令(编码格式)：SHOW CREATE DATABASE user;

创建gbk编码格式的数据库：CREATE DATABASE IF NOT EXISTS test CHARACTER SET gbk;

修改数据库的编码格式：ALTER DATABASE test CHARACTER SET=utf8 (SET utf8);

删除数据库：DROP DATABASE test;

MySQL查看表结构：SHOW COLUMNS FROM emp;

+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+

向数据表中插入记录 INSERT：（ INTO 可以省略）
1 所有字段都赋值：INSERT INTO emp VALUES('Tom',21,8976.85,'13638389438');
2 给指定的字段赋值：INSERT emp(name,salary) VALUES('John',6788.63);
查看数据表的记录 SELECT：
1 查询表中的所有字段：SELECT * FROM emp;
2 查询表中指定的字段：SELECT name,salary FROM emp;

MySQL空值与非空：

1.创建表tb1,username字段不为空,age字段允许为空
CREATE TABLE tb1(username VARCHAR(20) NOT NULL,age TINYINT UNSIGNED NULL);
2.向tb1表中添加数据
INSERT tb1 VALUES('TOM',NULL); --正确
INSERT tb1 VALUES('',20); --正确
INSERT tb1 VALUES(NULL,20); --错误

MySQL自动编号(主键自增)：AUTO_INCREMENT
自动编号,必须与主键组合使用；默认情况,起始值为1,每次的增量为1
1.创建一个主键自增的数据表tb2：CREATE TABLE tb2(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) NOT NULL);
2.查看表的数据结构：SHOW COLUMNS FROM tb2;
3.为部分字段赋值：INSERT tb2(username) VALUES('Tom');
INSERT tb2(username) VALUES('John');
4.主键自增不用手动赋值

MySQL主键约束：PRIMARY KEY
主键保证记录的唯一性
主键自动为NOT NULL
每张数据表只能有一个主键
在不设定主键自增的情况下,主键约束的字段可以赋值,但不可重复

MySQL唯一约束：UNIQUE KEY
唯一约束可以保证记录的唯一性
唯一约束的字段可以为 NULL
每张数据表可以存在多个唯一约束
创建id为主键自增,username为唯一约束的数据表：

CREATE TABLE tb3(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) NOT NULL UNIQUE KEY,age TINYINT UNSIGNED);
INSERT tb3(username,age) VALUES('Tom',22); --正确
INSERT tb3(username,age) VALUES('Tom',25); --错误

MySQL默认约束：DEFAULT
1.当插入数据时,如果没有明确为字段赋值,则会自动赋予默认值
创建性别为默认约束的数据表：

CREATE TABLE tb4(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) NOT NULL UNIQUE KEY,sex ENUM('1','2','3') DEFAULT '3');

MySQL外键约束：FOREIGN KEY
1. 保持数据的一致性,完整性
2. 实现一对一或一对多关系
3. 外键约束的要求：
3.1 父表(子表参照的表)和子表(具有外键列的表)必须使用相同的存储引擎,并且禁止使用临时表
3.2 数据表的存储引擎只能为InnoDB
3.3 外键列和参照列必须有相似的数据类型,其中数字的长度或是否有符号位必须相同.而字符的长度可以不同.
3.4 外键列和参照列必须创建索引,如果外键列不存在索引,MySQL将自动创建索引.参照列不存在索引,MySQL不会创建索引.
4. 编写数据表默认的存储引擎：
MySQL配置文件：

　　default-storage-engine=INNODB(默认的存储引擎就是INNODB)

5. 创建两张数据表
5.1 父表：

　　CREATE TABLE provinces(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,pname VARCHAR(20) NOT NULL); --省份数据表

5.2 子表：

CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL,pid BIGINT,FOREIGN KEY (pid) REFERENCES provinces (id)); 　　　　
--用户数据表,外键关联省份表（报错，不满足 外键约束的要求：数字的长度必须相同）
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL,pid SMALLINT,FOREIGN KEY (pid) REFERENCES provinces (id)); 
--用户数据表,外键关联省份表（报错，不满足 外键约束的要求：是否有符号位必须相同）
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL,pid SMALLINT UNSIGNED,FOREIGN KEY (pid) REFERENCES provinces (id)); 
--用户数据表,外键关联省份表（正确，满足外键约束的要求）

5.3 创建表是否存在索引：(主键在创建时，自动创建索引)　　

SHOW INDEXES FROM provinces; --显示索引
SHOW INDEXES FROM provinces\G; --显示索引，以列表形式呈现

6. 外键约束的参照操作
6-1. CASCADE:从父表删除或更新且自动删除或更新子表中匹配的列

CREATE TABLE user1(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL,pid SMALLINT UNSIGNED,FOREIGN KEY (pid) REFERENCES provinces (id) ON UPDATE CASCADE ON DELETE CASCADE);
DELETE FROM provinces WHERE id=2;

mysql> SELECT * FROM user1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | Tom      |    2 |
|  3 | John     |    1 |
|  4 | Rose     |    2 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> DELETE FROM provinces WHERE id=2;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM user1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  3 | John     |    1 |
+----+----------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  3 | C     |
+----+-------+
2 rows in set (0.00 sec)

mysql>

6-2. SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL.如果使用该选项,必须保证子表列没有指定NOT NULL.
6-3. RESTRICT:拒绝对父表的删除或更新操作

CREATE TABLE user1(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL,pid SMALLINT UNSIGNED,FOREIGN KEY (pid) REFERENCES provinces (id) ON UPDATE RESTRICT ON DELETE RESTRICT);
DELETE FROM provinces WHERE id=2;
#删除父表失败

6-4. NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同

MySQL的表级约束和列级约束
1. 对一个数据列建立的约束,称为列级约束
2. 对多个数据列建立的约束,称为表级约束
3. 列级约束既可以在列定义时声明，也可以在列定义后声明；表级约束只能在列定义后声明。
注：NOT NULL,DEFAULT 只有列级约束; PRIMARY KEY,UNIQUE KEY,FOREIGN KEY 都可存在表级或列级约束

posted on 2018-12-27 19:09 荆棘Study 阅读(266) 评论(0) 编辑收藏举报