mysql基础（二）-表

一. MySQL表数据类型
mysql中数据类型大致分为三类：数值、日期/时间和字符串。
MySQL支持所有标准SQL数值数据类型。

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

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期/时间类型

类型	大小（字节）	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDDHHMMSS	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

来源：http://www.runoob.com/mysql/mysql-data-types.html

二. 用SQL语句创建表
创建一个表名为courses为例，创建语法：

CREATE TABLE IF NOT EXISTS courses
(student VARCHAR(2) NOT NULL PRIMARY KEY,
 class VARCHAR(10) NOT NULL
)ENGINE=InnoDB;

其中IF NOT EXISTS在创建前判断是否存在，存在则不创建。不想字段为空则设置字段属性为NOT NULL，PRIMARY KEY为主键，可以多列定义。
三. 用SQL语句向表中添加数据
1、指定列名添加

insert into courses(student,class) values('C','Math');

2、不指定列名添加

insert into courses values('A','Math');

四. 用SQL语句删除表
1、DELETE

delete from courses where student='A';

2、DROP

drop table courses;

3、TRUNCATE

truncate table courses;

4、不同方式的区别:delete是删除表内数据；drop是删除表，truncate是删除表内所有数据。
五. 用SQL语句修改表
1、修改列名：将courses修改为course

 alter table courses rename course;

2、修改表中数据：修改数据时带上where限制修改哪一行，否则会将class一列全部修改成English

update courses set class='English' where student='B';

3、删除行

delete from course where student='A';

4、删除列

alter table course drop class;

5、新建列

alter table course add class char(10) not null;

6、新建行

insert into course values('C','Math');

#作业#
项目三：超过5名学生的课（难度：简单）
创建如下所示的courses 表，有: student (学生) 和 class (课程)。
例如,表:
mysql基础（二）-表

编写一个 SQL 查询，列出所有超过或等于5名学生的课。

 select distinct class from courses group by class having count(class)>=5;

应该输出:
mysql基础（二）-表
Note:
学生在每个课中不应被重复计算。

项目四：交换工资（难度：简单）
创建一个 salary表，如下所示，有m=男性和 f=女性的值。
例如:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A    | m   | 2500   |
| 2 | B    | f   | 1500   |
| 3 | C    | m   | 5500   |
| 4 | D    | f   | 500    |

mysql基础（二）-表

交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。
运行你所编写的查询语句之后，将会得到以下表:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A    | f | 2500   |
| 2 | B    | m   | 1500   |
| 3 | C    | f   | 5500   |
| 4 | D    | m   | 500    |

语句：

update salary set sex=(case when sex='m' then 'f'else 'm' end);

mysql基础（二）-表

MySQL 基础（三）- 表联结
#学习内容#
1、MySQL别名

为表取别名：

select * from salary as sa;

为字段取别名：

select salary as sal from salary;

2、INNER JOIN内连接

mysql inner join子句将一个表中的行与其他表中的行进行匹配，并允许从两个表中查询包含列的行记录。

mysql基础（二）-表
3、LEFT JOIN左连接

left join子句从匹配的左右表中查询选择行记录，连接左表中的所有行，在右表中找不到的也显示出来，使用NULL代替。

select student,sex,salary,class from courses left join salary on student=salary;

4、CROSS JOIN交叉连接

连接两个表，结果集包括两个表中的所有行，其中结果集中的每一行都是第一个表中行与第二个表中的行的组合。

 select * from courses cross join salary;

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State


mysql> create table person(
    -> Personld int,
    -> FirstName varchar(10),
    -> LastName varchar(10)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table Address(
    -> Addressld int,
    -> Personld int,
    -> City varchar(10),
    -> State varchar(10)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into person values(1,'zhang','zhan'),(2,'li','l'),(3,'zhou','zho');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into Address values(1,1,'南京','江宁'),(3,2,'湖南','怀化'),(2,3,'郑州','中原区');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select FirstName,LastName,City,State from person inner join Address on person.Personld=Address.Personld;
+-----------+----------+--------+-----------+
| FirstName | LastName | City   | State     |
+-----------+----------+--------+-----------+
| zhang     | zhan     | 南京   | 江宁      |
| li        | l        | 湖南   | 怀化      |
| zhou      | zho      | 郑州   | 中原区    |
+-----------+----------+--------+-----------+
3 rows in set (0.00 sec)

mysql>

项目六：删除重复的邮箱（难度：简单）
编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。
+----+---------+
| Id | Email |
+----+---------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+----+---------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person表应返回以下几行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | [email protected] |
| 2 | [email protected] |
+----+------------------+

mysql> select * from email;
+----+---------+
| id | Email   |
+----+---------+
|  1 | [email protected] |
|  2 | [email protected] |
|  3 | [email protected] |
+----+---------+
3 rows in set (0.00 sec)

mysql> delete e1 from email e1,email e2 where e1.email=e2.email and e1.id>e2.id;
Query OK, 1 row affected (0.01 sec)

mysql> select * from email;
+----+---------+
| id | Email   |
+----+---------+
|  1 | [email protected] |
|  2 | [email protected] |
+----+---------+
2 rows in set (0.00 sec)

mysql基础（二）-表

相关推荐