MySQL数据库基础第二天

最新推荐文章于 2022-07-13 15:02:00 发布

lyckid

最新推荐文章于 2022-07-13 15:02:00 发布

阅读量122

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/lyckid/article/details/103750998

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

测试自增长

建立表的时候，关键字段的完整性约束条件包括：PRIMARY_KEY(主键)、AUTO_INREMENT(自增长)、FOREIGN KEY(外键)、NOT NULL(非空)、UNIQUE KEY(唯一)、DEFAULT(默认值)

自增长必须是主键，但是主键不一定是自增长，自增长只对整数有效果。

mysql> USE test1;
Database changed
mysql> \T D:\mysqldemo\mysql20191229.txt
Logging to file 'D:\mysqldemo\mysql20191229.txt'
--可以通过\t 来结束日志功能
mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test1      |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS user5(
    -> id SMALLINT KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> DESC user5;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | smallint(6) | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.06 sec)

以上建立了一个带有自增长主键的表，在插入自增长值时，有多种方法：

--1
mysql> INSERT user5 VALUES(1,'KING');
Query OK, 1 row affected (0.05 sec)
--2
mysql> INSERT user5(username) VALUES('KING');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM user5;
+----+----------+
| id | username |
+----+----------+
|  1 | KING     |
|  2 | KING     |
+----+----------+
2 rows in set (0.00 sec)

mysql> INSERT user5 VALUES(111,'QUEEN');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM user5;
+-----+----------+
| id  | username |
+-----+----------+
|   1 | KING     |
|   2 | KING     |
| 111 | QUEEN    |
+-----+----------+
3 rows in set (0.00 sec)
--3
mysql> INSERT user5 VALUES(NULL,'KING');
Query OK, 1 row affected (0.04 sec)
--4
mysql> INSERT user5 VALUES(DEFAULT,'KING');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM user5;
+-----+----------+
| id  | username |
+-----+----------+
|   1 | KING     |
|   2 | KING     |
| 111 | QUEEN    |
| 112 | KING     |
| 113 | KING     |
+-----+----------+
5 rows in set (0.00 sec)

通过查看表的属性，可以看到当前自增长数值：AUTO_INCREMENT的值

mysql> SHOW CREATE TABLE user5;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user5 | CREATE TABLE `user5` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=114 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

还可以在创建表时，指定自增长值：

--5
mysql> CREATE TABLE IF NOT EXISTS user6(
    -> id SMALLINT KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> )AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.07 sec)

非空约束

标志非空的字段（NOT NULL），必须要填写，如网站登录的账户密码。

mysql> CREATE TABLE IF NOT EXISTS user7(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> DESC user7;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| password | varchar(20)         | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> INSERT user7(username,password) VALUES('KING','KING');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT user7(username,password,age) VALUES('KING1','KING1',12);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user7;
+----+----------+----------+------+
| id | username | password | age  |
+----+----------+----------+------+
|  1 | KING     | KING     | NULL |
|  2 | KING1    | KING1    |   12 |
+----+----------+----------+------+
2 rows in set (0.00 sec)

测试默认值

一般非空约束与默认值配合使用，一般非空约束没给值，就以默认值填充。

mysql> CREATE TABLE IF NOT EXISTS user8(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED DEFAULT 18,
    -> addr VARCHAR(20) NOT NULL DEFAULT '北京',
    -> sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT user8(username,password) VALUES('KING','KING');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT user8 VALUES(3,'queen','queen',DEFAULT,DEFAULT,'保密');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM user8;
+----+----------+----------+------+--------+--------+
| id | username | password | age  | addr   | sex    |
+----+----------+----------+------+--------+--------+
|  1 | KING     | KING     |   18 | 北京   | 男     |
|  2 | queen    | queen    |   29 | 上海   | 保密   |
|  3 | queen    | queen    |   18 | 北京   | 保密   |
+----+----------+----------+------+--------+--------+
3 rows in set (0.00 sec)

测试唯一性约束条件

标志了唯一性后，不可以出现重复，但NULL不算范围内。

mysql> CREATE TABLE IF NOT EXISTS user9(
    -> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> card CHAR(18) UNIQUE
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> DESC user9;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| card     | char(18)            | YES  | UNI | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

经上总结，创建一个表的时候大概需要以下语句：

CREATE TABLE IF NOT EXISTS tbl_name(
字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT] [[PRIMARY] KEY | UNIQUE [KEY]] [AUTO_INCREMENT]
)ENGINE=INNODB, CHARSET-UTF8 AUTO_INCREMENT=100;

表结构操作

重命名数据表

修改表名：

--1
ALTER TABLE tbl_name RENAME [TO|AS] new_name;
--2
RENAME TABLE tbl_name TO new_name

使用方法如下：

mysql> CREATE TABLE IF NOT EXISTS user10(
    -> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> password VARCHAR(32) NOT NULL,
    -> email VARCHAR(50) NOT NULL DEFAULT 'XXXXXX@qq.com;',
    -> age TINYINT UNSIGNED DEFAULT 18,
    -> sex ENUM('男','女','保密') DEFAULT '保密',
    -> addr VARCHAR(200) NOT NULL DEFAULT '北京',
    -> salary FLOAT(6,2),
    -> regTime INT UNSIGNED,
    -> face CHAR(100) NOT NULL DEFAULT 'default.jpg'
    -> );
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> ALTER TABLE user10 RENAME AS user11;
Query OK, 0 rows affected (0.08 sec)

mysql> RENAME TABLE user11 TO user10;
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| test1           |
| test10          |
| test11          |
| test2           |
| test3           |
| test4           |
| test5           |
| test6           |
| test7           |
| test8           |
| user            |
| user10          |
| user5           |
| user6           |
| user7           |
| user8           |
| user9           |
+-----------------+
17 rows in set (0.05 sec)

添加字段：

ALTER TABLE tbl_name ADD 字段名称 字段类型[完整性约束条件][FIRST|AFTER 字段名称]

如果省略位置，就是添加在最后。
可以同时添加多个字段：

mysql> ALTER TABLE user10
    ->
    -> ADD test4 INT NOT NULL DEFAULT 123 AFTER password,
    ->
    -> ADD test5 FLOAT(6,2) FIRST,
    ->
    -> ADD test6 SET('A','B','C');
Query OK, 0 rows affected, 1 warning (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> DESC user10;
+----------+----------------------------+------+-----+----------------+----------------+
| Field    | Type                       | Null | Key | Default        | Extra          |
+----------+----------------------------+------+-----+----------------+----------------+
| test5    | float(6,2)                 | YES  |     | NULL           |                |
| id       | tinyint(3) unsigned        | NO   | PRI | NULL           | auto_increment |
| username | varchar(20)                | NO   | UNI | NULL           |                |
| password | varchar(32)                | NO   |     | NULL           |                |
| test4    | int(11)                    | NO   |     | 123            |                |
| email    | varchar(50)                | NO   |     | XXXXXX@qq.com; |                |
| age      | tinyint(3) unsigned        | YES  |     | 18             |                |
| sex      | enum('男','女','保密')     | YES  |     | 保密           |                |
| addr     | varchar(200)               | NO   |     | 北京           |                |
| salary   | float(6,2)                 | YES  |     | NULL           |                |
| regTime  | int(10) unsigned           | YES  |     | NULL           |                |
| face     | char(100)                  | NO   |     | default.jpg    |                |
| test6    | set('A','B','C')           | YES  |     | NULL           |                |
+----------+----------------------------+------+-----+----------------+----------------+
13 rows in set (0.00 sec)

删除字段：

ALTER TABLE tbl_name DROP 字段名称

删除也可以一次完成多个：

mysql> ALTER TABLE user10
    ->
    -> DROP test4,
    ->
    -> DROP test5,
    ->
    -> DROP test6;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user10;
+----------+----------------------------+------+-----+----------------+----------------+
| Field    | Type                       | Null | Key | Default        | Extra          |
+----------+----------------------------+------+-----+----------------+----------------+
| id       | tinyint(3) unsigned        | NO   | PRI | NULL           | auto_increment |
| username | varchar(20)                | NO   | UNI | NULL           |                |
| password | varchar(32)                | NO   |     | NULL           |                |
| email    | varchar(50)                | NO   |     | XXXXXX@qq.com; |                |
| age      | tinyint(3) unsigned        | YES  |     | 18             |                |
| sex      | enum('男','女','保密')     | YES  |     | 保密           |                |
| addr     | varchar(200)               | NO   |     | 北京           |                |
| salary   | float(6,2)                 | YES  |     | NULL           |                |
| regTime  | int(10) unsigned           | YES  |     | NULL           |                |
| face     | char(100)                  | NO   |     | default.jpg    |                |
+----------+----------------------------+------+-----+----------------+----------------+
10 rows in set (0.00 sec)

也可以一次添加一个删除一个：

mysql> ALTER TABLE user10
    ->
    -> ADD test INT NOT NULL DEFAULT 10,
    ->
    -> DROP addr;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user10;
+----------+----------------------------+------+-----+----------------+----------------+
| Field    | Type                       | Null | Key | Default        | Extra          |
+----------+----------------------------+------+-----+----------------+----------------+
| id       | tinyint(3) unsigned        | NO   | PRI | NULL           | auto_increment |
| username | varchar(20)                | NO   | UNI | NULL           |                |
| password | varchar(32)                | NO   |     | NULL           |                |
| email    | varchar(50)                | NO   |     | XXXXXX@qq.com; |                |
| age      | tinyint(3) unsigned        | YES  |     | 18             |                |
| sex      | enum('男','女','保密')     | YES  |     | 保密           |                |
| salary   | float(6,2)                 | YES  |     | NULL           |                |
| regTime  | int(10) unsigned           | YES  |     | NULL           |                |
| face     | char(100)                  | NO   |     | default.jpg    |                |
| test     | int(11)                    | NO   |     | 10             |                |
+----------+----------------------------+------+-----+----------------+----------------+
10 rows in set (0.00 sec)

修改字段：

ALTER TABLE tbl_name MODIFY 字段名称 字段类型[完整性约束条件][FIRST|AFTER 字段名称之后]

不填写[完整性约束条件]，就自动清空了，不填写位置，位置不变。

修改字段名称

ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型[完整性约束条件][FIRST|AFTER 字段名称之后]

如果不修改名字而是修改字段其它属性，则新旧名字一样即可。

数据的操作（DML）

插入数据操作

不指定具体的字段名INSERT [INTO] tbl_name VALUES|VALUE(值...);
列出指定字段 INSERT [INTO] tbl_name(字段名1,..) VALUES|VALUE(值...);
同时插入多条记录 INSERT [INTO] tbl_name[(字段名,..)] VALUES(值...),(值...)
通过SET形式插入记录 INSERT [INTO] tbl_name SET 字段名称=值,...
将查询结果插入到表中 INSERT [INTO] tbl_name[(字段名,..)] SELECT 字段名称 FROM tbl_name [WHERE 条件]

对于第二条的字段名可以不按照表内原有顺序写，只要自己对的上即可。

-- testUSER表只有id,username两个关键字段，会将表user中所有两个字段插入进去
INSERT testUSER SELECT id,username FROM user;
-- 错误写法,user表内容比testUser多
INSERT testUser SELECT * FROM user;
-- 值将user中的username全部插入到表中
INSERT testUSER(username) SELECT username FROM user;

更新操作

UPDATE tbl_name SET 字段名称=值,...[WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

如果不加where条件，表中所有数据都会被更新。同时更改可以使用表达式：

UPDATE user SET password='king123',email='123@qq.com',age=99 WHERE id=1；

UPDATE user SET age=age-5 WHERE ID>=3;
--表中数据更新称默认值
UPDATE user SET age=DEFAULT WHERE username='A';

删除数据

DELETE FROM tbl_name[WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

如果不加条件，表中所有数据都被删除。删除表中某条记录时，AUTO_INCREMENT的值不会被重置。每次删除后，在插入原来位置的值需要修改自增长值：

ALTER TABLE tbl_name AUTO_INCREMENT=1;

彻底清空数据表

TRUNCATE [TABLE] tbl_name

清除表中的数据，并且重置自增长值为1。

查询数据操作（DQL）

查询记录：

SELECT select_expr [,select_expr..]
[
 FROM table_references
 [WHERE 条件]
 [GROUP BY {col_name | position} [ASC | DESC]，...分组]
 [HAVING 条件 对分组结果进行二次筛选]
 [ORDER BY {col_name | position} [ASC | DESC]，...排序]
 [LIMIT 限制显示条数]
]

查询表达式：
每一个表达式表示想要的一列，必须至少有一列，多个列之间以逗号分隔，*表示所有列，tbl_name.*可以表示命名表的所有列。
查询表达式可以使用[AS]alias_name为其赋予别名。

SELECT * FROM cms_admin;
--上下两句效果一样
SELECT csm_admin.* FROM csm_admin;
--多个字段以逗号分割
SELECT id,username FROM csm_admin;
--表来自于哪个数据库下db_name.tbl_name,此时可以不用 USE db_name
SELECT id,username,role FROM cms.cms_admin;
--字段来自哪个表
SELECT cms_admin.id,cms_admin.username,cms_admin.role FROM cms.cms_admin;
-- 给表起别名
SELECT id，username FROM cms_admin AS a;
SELECT id，username FROM cms_admin a;

SELECT a.id，a.username,a.role FROM cms_admin a;

-- 给字段起别名,提出的数据显示就会以别名显示，中文作为字符串需要加引号，AS可以省略，但一般不省略因为可读性差
SELECT id AS '编号'，username AS '用户名' FROM cms_admin;
-- 也可以给查询结果前添加表达式,查询出的每行结果前有1,2,3,4,5
SELECT 1,2,3,4,5,id FROM cms_admin;

带条件的查询

WHERE条件

查询条件	符号
比较	=、<=、<、>、>=、!=、<>、!>、!<、<=>
指定范围	BETWEEN AND、NOT BETWEEN AND
指定结合	IN、NOT IN
匹配字符	LIKE、 NOT LIKE
是否为控制	IS NULL 、 IS NOT NULL
多个查询条件	AND 、 OR

--查询编号为1的用户，不符合条件的返回空
SELECT id,username,email FROM cms_user WHERE id=1;
SELECT id,username,email FROM cms_user WHERE id>=1;
-- <>也是不等于
SELECT id,username,email FROM cms_user WHERE id !=1;
-- 如表中某个数为NULL，在查询时指定条件为NULL，查询不出来，此时可以使用<=>来检测，除了=以外还可以多检测NULL
SELECT id,username,email FROM cms_user WHERE id<=>NULL;

SELECT * FROM cms_user WHERE age is NULL;
--查询编号在3-10之间的用户
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;

--指定集合，用于非连续的范围
SELECT * FROM cms_user WHERE id IN(1,3,4,7,9,110);

-- 模糊查询
--%：代表0个一个或多个任意字符
--_:代表1个任意字符
--表示张前后都有未知个数字符
SELECT * FROM cms_user WHERE username LIKE '%张%';
--等于没写条件
SELECT * FROM cms_user WHERE username LIKE '%';
--查询用户名只有三位的
SELECT * FROM cms_user WHERE username LIKE '___';
--没用使用%和_,相当于等号=
SELECT * FROM cms_user WHERE username LIKE 'king';

分组查询

通过GROUP BY查询结果分组

--只显示每组第一个数据
SELECT * FROM cms_user GROUP BY proID;

--按照关键字的位置来判断关键字
SELECT * FROM cms_user GROUP BY 7;

--按照多个字段分组,再第一个proID的每个组里再按照sex分组
SELECT * FROM cms_user GROUP BY proID,sex;

可以通过GROUP_CONCAT获得组内详情：

SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;
--查询多个分组详情
SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime) FROM cms_user GROUP BY proId;

还可以配合聚合函数使用：
COUNT()统计总数
MAX()统计最大值
MIN()统计最小值
AVG()统计平均值
SUM()统计求和

--COUNT(*)固定用法没统计组内所有数量，不分组的话就是表中所有的，如果把*换成某个关键字，也是同样效果，但特殊它不统计NULL，如果关键字里面有NULL值，则不统计。
SELECT id,sex,GROUP_CONCAT(username),COUNT(*) FROM cms_user GROUP BY sex;

--查询编号，性别，用户详情，组中总人数，组中最大年龄，最小年龄，平均年龄，以及年龄总和按照性别分组
SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totaluser,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_admin 
GROUP BY sex;

配合WITH ROLLUP会统计所有记录上的总和、最大值、最小值等。就是把COUNT的值求和、MAX的值求出最大值、MIN的值求出最小值、SUM值求总和、AVG值求均值。

SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totaluser,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_admin 
GROUP BY sex
WITH ROLLUP;

分组结果二次筛选

HAVING 条件只能在分组后使用，必须配合GROUP BY。

--查询组中人数大于2并且最大年龄大于60
SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totaluser,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_admin 
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;
--
SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totaluser,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_admin 
WHERE id>=2
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60；

lyckid

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库基础第二天

文章目录测试自增长非空约束测试默认值测试唯一性约束条件表结构操作重命名数据表数据的操作（DML）插入数据操作更新操作删除数据查询数据操作（DQL）带条件的查询分组查询分组结果二次筛选测试自增长建立表的时候，关键字段的完整性约束条件包括：PRIMARY_KEY(主键)、AUTO_INREMENT(自增长)、FOREIGN KEY(外键)、NOT NULL(非空)、UNIQUE KEY(唯一)、D...
复制链接

扫一扫

专栏目录