7天快速掌握MySQL-DAY3

最新推荐文章于 2022-10-05 15:57:12 发布

dbLenis

最新推荐文章于 2022-10-05 15:57:12 发布

阅读量259

点赞数

首先公布一下DAY2的作业答案。

项目一：

mysql> select distinct Email from email    -> group by Email    -> having count(Email) > 1;+---------+| Email   |+---------+| a@b.com |+---------+1 row in set (0.01 sec)

项目二：

mysql> select name, population, area    -> from World    -> where area > 3000000 or (population > 25000000 and gdp > 20000000);+-------------+------------+---------+| name        | population | area    |+-------------+------------+---------+| Afghanistan |   25500100 |  652230 || Algeria     |   37100000 | 2381741 |+-------------+------------+---------+2 rows in set (0.00 sec)

你正确查询出结果了吗？咱们继续DAY3。

1. mysql表的数据类型

1.1 数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

1.2 日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性。

1.3 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

MySQL 5.0 以上的版本：

1、一个汉字占多少长度与编码有关：

UTF－8：一个汉字＝3个字节

GBK：一个汉字＝2个字节

2、varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别

3、MySQL 检查长度，可用 SQL 语言来查看：

select LENGTH(fieldname) from tablename;

2. 用SQL语句创建表

mysql> CREATE TABLE World3 (  --设置表名称    -> name VARCHAR(50) NOT NULL PRIMARY KEY,    --设置列名、列类型、列大小、约束、主键    -> continent VARCHAR(50) NOT NULL,    -> area INT NOT NULL,    -> population INT NOT NULL,    -> gdp INT NOT NULL    -> );Query OK, 0 rows affected (0.02 sec)

3. 增

--插入数据，不指定列名mysql> INSERT INTO World3 VALUES( 'Afghanistan', 'Asia',652230,25500100,20343000);Query OK, 1 row affected (0.01 sec)--插入数据，指定列名mysql> INSERT INTO World3 (name, continent, area, population, gdp) VALUES( 'Angola' , 'Africa' ,1246700,20609294,100990000);Query OK, 1 row affected (0.00 sec)

4. 删

--DELETE 删除指定条目，保留表结构mysql> select * from World3;+-------------+-----------+---------+------------+-----------+| name        | continent | area    | population | gdp       |+-------------+-----------+---------+------------+-----------+| Afghanistan | Asia      |  652230 |   25500100 |  20343000 || Albania     | Europe    |   28748 |    2831741 |  12960000 || Angola      | Africa    | 1246700 |   20609294 | 100990000 |+-------------+-----------+---------+------------+-----------+3 rows in set (0.00 sec)
mysql> delete from World3 where name = 'Angola';Query OK, 1 row affected (0.01 sec)
mysql> select * from World3;+-------------+-----------+--------+------------+----------+| name        | continent | area   | population | gdp      |+-------------+-----------+--------+------------+----------+| Afghanistan | Asia      | 652230 |   25500100 | 20343000 || Albania     | Europe    |  28748 |    2831741 | 12960000 |+-------------+-----------+--------+------------+----------+2 rows in set (0.00 sec)--TRUNCATE  截断表，清空数据，保留表结构，速度快mysql> TRUNCATE table World3;Query OK, 0 rows affected (0.01 sec)
mysql> select * from World3;Empty set (0.00 sec)--DROP 删除数据内容及数据表，慎用！mysql> Drop table World3;Query OK, 0 rows affected (0.01 sec)
mysql> select * from World3;ERROR 1146 (42S02): Table 'yiibaidb.world3' doesn't exist

MySql的Delete、Truncate、Drop区别

相同点：

　　truncate 和不带 where 子句的 delete，以及 drop 都会删除表内的数据

不同点:

　　1. truncate 和 delete 只删除数据不删除表的结构(定义)

　　drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index);依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

　　2. delete 语句是数据库操作语言(dml)，这操作会放到rollback segement 中，事务提交之后才生效;如果有相应的 trigger，执行的时候将被触发。

　　truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

　　3. delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动

　　显然 drop 语句将表所占用的空间全部释放。

　　truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage;truncate 会将高水线复位(回到最开始)。

　　4. 速度，一般来说: drop> truncate > delete

　　5. 安全性：小心使用 drop 和 truncate，尤其没有备份的时候.否则哭都来不及

　　使用上,想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大.

　　想删除表,当然用 drop

　　想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。

　　如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

delete延伸

# 单表删除内容mysql> select * from students2;+-----+-------+--------+---------+| sid | sname | gender | dept_id |+-----+-------+--------+---------+|   1 | 555   | 1      |       2 ||   2 | 555   | 2      |       2 ||   3 | 555   | 3      |       2 ||   4 | 555   | 3      |       1 ||   5 | 555   | 1      |       1 |+-----+-------+--------+---------+5 rows in set (0.00 sec)
mysql> delete from students2 where sid=4;Query OK, 1 row affected (0.05 sec)
mysql> select * from students2;+-----+-------+--------+---------+| sid | sname | gender | dept_id |+-----+-------+--------+---------+|   1 | 555   | 1      |       2 ||   2 | 555   | 2      |       2 ||   3 | 555   | 3      |       2 ||   5 | 555   | 1      |       1 |+-----+-------+--------+---------+4 rows in set (0.00 sec)
# 多表删除内容mysql> select * from person;+----------+-----------+----------+| personid | firstname | lastname |+----------+-----------+----------+|      101 | ming      | Yao      ||      102 | lei       | Wu       ||      103 | zhi       | Zheng    |+----------+-----------+----------+3 rows in set (0.00 sec)
mysql> select * from address;+-----------+----------+----------+-------+| addressid | personid | city     | state |+-----------+----------+----------+-------+|      8000 |     9999 | xian     | UA    ||      8008 |      102 | beijing  | AB    ||      9009 |      103 | hangzhou | AC    |+-----------+----------+----------+-------+3 rows in set (0.00 sec)
mysql> delete p1, a1    -> from person p1 inner join address a1    -> where p1.personid = a1.personid;Query OK, 4 rows affected (0.01 sec)
mysql> select * from person;+----------+-----------+----------+| personid | firstname | lastname |+----------+-----------+----------+|      101 | ming      | Yao      |+----------+-----------+----------+1 row in set (0.00 sec)
mysql> select * from address;+-----------+----------+------+-------+| addressid | personid | city | state |+-----------+----------+------+-------+|      8000 |     9999 | xian | UA    |+-----------+----------+------+-------+1 row in set (0.00 sec)

5. 改

--修改列名mysql> select * from World3;+-------------+-----------+---------+------------+-----------+| name        | continent | area    | population | gdp       |+-------------+-----------+---------+------------+-----------+| Afghanistan | Asia      |  652230 |   25500100 |  20343000 || Albania     | Europe    |   28748 |    2831741 |  12960000 || Angola      | Africa    | 1246700 |   20609294 | 100990000 |+-------------+-----------+---------+------------+-----------+3 rows in set (0.00 sec)
mysql> alter table World3 change  column name name_new varchar(50);Query OK, 3 rows affected (0.06 sec)Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from World3;+-------------+-----------+---------+------------+-----------+| name_new    | continent | area    | population | gdp       |+-------------+-----------+---------+------------+-----------+| Afghanistan | Asia      |  652230 |   25500100 |  20343000 || Albania     | Europe    |   28748 |    2831741 |  12960000 || Angola      | Africa    | 1246700 |   20609294 | 100990000 |+-------------+-----------+---------+------------+-----------+3 rows in set (0.00 sec)--修改列数据类型 - V1mysql> alter table World3 modify name_new char(30);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0
mysql> describe World3;+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| name_new   | char(30)    | NO   | PRI |         |       || continent  | varchar(50) | NO   |     | NULL    |       || area       | int(11)     | NO   |     | NULL    |       || population | int(11)     | NO   |     | NULL    |       || gdp        | int(11)     | NO   |     | NULL    |       |+------------+-------------+------+-----+---------+-------+5 rows in set (0.01 sec)--修改列数据类型 - V2mysql> alter table World3 change name_new address  char(40);Query OK, 3 rows affected (0.04 sec)Records: 3  Duplicates: 0  Warnings: 0
mysql> describe World3;+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| address    | char(40)    | NO   | PRI |         |       || continent  | varchar(50) | NO   |     | NULL    |       || area       | int(11)     | NO   |     | NULL    |       || population | int(11)     | NO   |     | NULL    |       || gdp        | int(11)     | NO   |     | NULL    |       |+------------+-------------+------+-----+---------+-------+5 rows in set (0.01 sec)--修改表中的数据mysql> select * from World3;+-------------+-----------+---------+------------+-----------+| country     | continent | area    | population | gdp       |+-------------+-----------+---------+------------+-----------+| Afghanistan | Asia      |  652230 |   25500100 |  20343000 || Albania     | Europe    |   28748 |    2831741 |  12960000 || Angola      | Africa    | 1246700 |   20609294 | 100990000 |+-------------+-----------+---------+------------+-----------+3 rows in set (0.00 sec)
mysql> UPDATE World3 SET country ='China',area = 9600000 WHERE country = 'Afghanistan';Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from World3;+---------+-----------+---------+------------+-----------+| country | continent | area    | population | gdp       |+---------+-----------+---------+------------+-----------+| Albania | Europe    |   28748 |    2831741 |  12960000 || Angola  | Africa    | 1246700 |   20609294 | 100990000 || China   | Asia      | 9600000 |   25500100 |  20343000 |+---------+-----------+---------+------------+-----------+3 rows in set (0.00 sec)--删除行mysql> select * from World3;+---------+-----------+---------+------------+-----------+| country | continent | area    | population | gdp       |+---------+-----------+---------+------------+-----------+| Albania | Europe    |   28748 |    2831741 |  12960000 || Angola  | Africa    | 1246700 |   20609294 | 100990000 || China   | Asia      | 9600000 |   25500100 |  20343000 |+---------+-----------+---------+------------+-----------+3 rows in set (0.00 sec)
mysql> DELETE FROM World3 WHERE country = 'China';Query OK, 1 row affected (0.00 sec)
mysql> select * from World3;+---------+-----------+---------+------------+-----------+| country | continent | area    | population | gdp       |+---------+-----------+---------+------------+-----------+| Albania | Europe    |   28748 |    2831741 |  12960000 || Angola  | Africa    | 1246700 |   20609294 | 100990000 |+---------+-----------+---------+------------+-----------+2 rows in set (0.00 sec)--删除列mysql> select * from World3;+---------+-----------+---------+------------+-----------+| country | continent | area    | population | gdp       |+---------+-----------+---------+------------+-----------+| Albania | Europe    |   28748 |    2831741 |  12960000 || Angola  | Africa    | 1246700 |   20609294 | 100990000 |+---------+-----------+---------+------------+-----------+2 rows in set (0.00 sec)
mysql> alter table World3 drop column gdp;Query OK, 2 rows affected (0.05 sec)Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from World3;+---------+-----------+---------+------------+| country | continent | area    | population |+---------+-----------+---------+------------+| Albania | Europe    |   28748 |    2831741 || Angola  | Africa    | 1246700 |   20609294 |+---------+-----------+---------+------------+2 rows in set (0.00 sec)--新建列mysql> alter table World3 add  column gdp_2 int(11);Query OK, 2 rows affected (0.03 sec)Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from World3;+---------+-----------+---------+------------+-------+| country | continent | area    | population | gdp_2 |+---------+-----------+---------+------------+-------+| Albania | Europe    |   28748 |    2831741 |  NULL || Angola  | Africa    | 1246700 |   20609294 |  NULL |+---------+-----------+---------+------------+-------+2 rows in set (0.00 sec)
--新建行mysql> INSERT INTO World3 VALUES( 'Japan' , 'Asia' ,123456,10000000,98000000);Query OK, 1 row affected (0.01 sec)
mysql> select * from World3;+---------+-----------+---------+------------+----------+| country | continent | area    | population | gdp_2    |+---------+-----------+---------+------------+----------+| Albania | Europe    |   28748 |    2831741 |     NULL || Angola  | Africa    | 1246700 |   20609294 |     NULL || Japan   | Asia      |  123456 |   10000000 | 98000000 |+---------+-----------+---------+------------+----------+3 rows in set (0.00 sec)

6. 作业

6.1 项目三：超过5名学生的课（难度：简单）

创建如下所示的courses 表 ，有: student (学生) 和 class (课程)。例如,表:+---------+------------+| student | class      |+---------+------------+| A       | Math       || B       | English    || C       | Math       || D       | Biology    || E       | Math       || F       | Computer   || G       | Math       || H       | Math       || I       | Math       || A       | Math       |+---------+------------+
编写一个 SQL 查询，列出所有超过或等于5名学生的课。应该输出:+---------+| class   |+---------+| Math    |+---------+Note:学生在每个课中不应被重复计算。--建表mysql> CREATE TABLE courses (    -> student CHAR(1) NOT NULL ,    -> class VARCHAR(20) NOT NULL    -> );Query OK, 0 rows affected (0.01 sec)--查看数据结构mysql> describe courses;+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| student | char(1)     | NO   |     | NULL    |       || class   | varchar(20) | NO   |     | NULL    |       |+---------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)--插入数据mysql> insert into courses VALUES('D', 'Biology');Query OK, 1 row affected (0.01 sec)
mysql> insert into courses VALUES('E', 'Math');Query OK, 1 row affected (0.01 sec)
mysql> insert into courses VALUES('F', 'Computer');Query OK, 1 row affected (0.01 sec)
mysql> insert into courses VALUES('G', 'Math');Query OK, 1 row affected (0.01 sec)
mysql> insert into courses VALUES('H', 'Math');Query OK, 1 row affected (0.01 sec)
mysql> insert into courses VALUES('I', 'Math');Query OK, 1 row affected (0.01 sec)
mysql> insert into courses VALUES('A', 'Math');Query OK, 1 row affected (0.01 sec)--核查插入结果mysql> select * from courses;+---------+----------+| student | class    |+---------+----------+| A       | Math     || B       | English  || C       | Math     || D       | Biology  || E       | Math     || F       | Computer || G       | Math     || H       | Math     || I       | Math     || A       | Math     |+---------+----------+10 rows in set (0.00 sec)

6.2 项目四：交换工资（难度：简单）

创建一个 salary表，如下所示，有m=男性 和 f=女性的值 。例如:| id | name | sex | salary ||----|------|-----|--------|| 1  | A    | m   | 2500   || 2  | B    | f   | 1500   || 3  | C    | m   | 5500   || 4  | D    | f   | 500    |
交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。运行你所编写的查询语句之后，将会得到以下表:| id | name | sex | salary ||----|------|-----|--------|| 1  | A    | f   | 2500   || 2  | B    | m   | 1500   || 3  | C    | f   | 5500   || 4  | D    | m   | 500    |--建表sqlmysql> CREATE TABLE salary (    -> id int(10) not null ,    -> name VARCHAR(20) not null,    -> sex char(1) not null,    -> salary int(10)    -> );Query OK, 0 rows affected (0.02 sec)--查询数据结构mysql> describe salary;+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id     | int(10)     | NO   |     | NULL    |       || name   | varchar(20) | NO   |     | NULL    |       || sex    | char(1)     | NO   |     | NULL    |       || salary | int(10)     | YES  |     | NULL    |       |+--------+-------------+------+-----+---------+-------+4 rows in set (0.01 sec)--插入数据mysql> insert into salary values(1,'A','m',2500);Query OK, 1 row affected (0.01 sec)mysql> insert into salary values(2,'B','f',1500);Query OK, 1 row affected (0.01 sec)mysql> insert into salary values(3,'C','m',5500);Query OK, 1 row affected (0.01 sec)mysql> insert into salary values(4,'D','f',500);Query OK, 1 row affected (0.01 sec)--核查插入结果mysql> select * from salary;+----+------+-----+--------+| id | name | sex | salary |+----+------+-----+--------+|  1 | A    | m   |   2500 ||  2 | B    | f   |   1500 ||  3 | C    | m   |   5500 ||  4 | D    | f   |    500 |+----+------+-----+--------+4 rows in set (0.00 sec)