MySQL第二次使用怎么连接_MySQL第二次任务

2.1 MySQL 基础 (二)- 表操作

#学习内容#

1. 用SQL语句向表中添加数据

(1)语句：INSERT语句

1 insert into table

2 values (value1,value2,value3);

必须给每一列提供一个值，如果某一列没有值，应当给NULL。

(2)多种添加方式

a.插入完整的行

不指定列名：

默认按照表的列名顺序来，SQL 语句高度依赖于表中列的定义次序，还依赖于其容易获得的次序信息。这种语句虽然简单，但是不安全。代码实例如(1)所示

指定列名：

表名的括号里给出列名，在插入行时， DBMS 将用 VALUES 列表中的相应值填入列表中的对应项。 VALUES 中的第一个值对应于第一个指定列名，第二个值对应于第二个列名，如此等等。因为提供了列名， VALUES 必须以其指定的次序匹配指定的列名，不一定按各列出现在表中的实际次序。其优点是，即使表的结构改变，这条INSERT 语句仍然能正确工作。

inset into table(field1,field2,field3)values(value1,value2,value3);

b.插入检索出的数据

insert intotable1(field1,field2)select (field1,field2) from table2;

PS: 复制表：

create table table1 as

select * from table2;

2. 用SQL语句删除表

(1)语句解释：

DELETE：从表中删除特定行

delete from table

where field1=value;

DROP:删除整个表，包括表结构

DROP TABLE Customer;

TRUNCATE：清除表内容，但保留表结构，等同于删除所有行

TRUNCATE TABLE Customer;

3. 用SQL语句修改表

(1)修改列名

注意一定要是新列名+新数据类型

ALTER Tabletable1

CHANGE Fileld newnamevarchar(30);

(2) 修改表中数据

可使用UPDATE语句，包括：

要更新的表

列名和新值

确定要更新哪些行的过滤条件

UPDATE Table1

SET field1=value1where field2=value2

(3)  删除列

alter table table1 drop column name; --删除表列

(4)新建列

alter table table1 add column newfield varchar(30);

4. 自动增加关键字

sql中的 auto_increment 关键字可以用在建立表格时起到自动增加的作用，不用手动输入。多用于id的增加。

如下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键：

CREATE TABLE Persons

(

P_Id int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

PRIMARY KEY (P_Id)

)

默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100

如果要在已有的字段上设立自增字段，语法为：

ALTER TABLE tbl_name

MODIFY col_name Datatype auto_increment;

其中，数据类型是无论你改不改都要加上的，其他的not null等属性可以自己选择加或不加。

要在 "Persons" 表中插入新记录，我们不必为 "P_Id" 列规定值(会自动添加一个唯一的值)：

INSERT INTO TABLE Persons(FirstName,LastName)

VALUES ('Bill','Gates')

上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"P_Id" 会被赋予一个唯一的值。"FirstName" 会被设置为 "Bill"，"LastName" 列会被设置为 "Gates"。

但是值得注意的是， Persons(FirstName,LastName) 中，一定要给定除了自增字段之外的字段，不然会报错。

#作业#

创建如下所示的 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:

学生在每个课中不应被重复计算。

代码：

create table courses (student varchar(30) NOT NULL, class varchar(30) NOT NULL);INSERT INTOcoursesVALUES('A', 'Math'),

('A', 'Math'),

('B', 'English'),

('C', 'Math'),

('D', 'Bio'),

('E', 'Math'),

('F', 'Comp'),

('G', 'Math'),

('H', 'Math'),

('I', 'Math');select class from (select distinct * fromcourses) tgroup byt.classhaving count(*)>5;

项目四：交换工资(难度：简单)

创建一个 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    |

代码：

CREATE TABLEsalary (

idINT NOT NULL auto_increment PRIMARY KEY,

NAMEVARCHAR (30) NOT NULL,

sexVARCHAR (1) NOT NULL,

salaryDOUBLE NOT NULL);INSERT INTOsalary (NAME, sex, salary)VALUES("A", "m",2500);INSERT INTOsalary (NAME, sex, salary)VALUES("B", "f",1500);INSERT INTOsalary (NAME, sex, salary)VALUES("C", "m",5500);INSERT INTOsalary (NAME, sex, salary)VALUES("D", "f",500);

updatesalaryset sex=(case when sex='f' then 'm'

else 'f' end);

2.2 MySQL 基础 (三)- 表联结

#学习内容#

MySQL别名

INNER JOIN

LEFT JOIN

CROSS JOIN

自连接

UNION

以上几种方式的区别和联系

#作业#

项目五：组合两张表 (难度：简单)

在数据库中创建表1和表2，并各插入三行数据(自己造)

表1: Person

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+

| PersonId | int |

| FirstName | varchar |

| LastName | varchar |

+-------------+---------+

PersonId 是上表主键

表2: Address

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+t

| AddressId | int |

| PersonId | int |

| City | varchar |

| State | varchar |

+-------------+---------+

AddressId 是上表主键

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

代码：

建表：

create tableperson (

PersonIdint not null primary key auto_increment,--设置自增字段

FirstName varchar(30) not null,

LastNamevarchar(30) not null);INSERT INTO Person(FirstName,LastName) VALUES('James','Lee');INSERT INTO Person(FirstName,LastName) VALUES('Jack','Tom');INSERT INTO Person(FirstName,LastName) VALUES('Sam','Brown');create tableAddress (

AddressIdint not null primary keyauto_increment,

PersonIdint not null referencesperson.PersonId,

Cityvarchar(30) not null,

Statevarchar(30) not null);INSERT INTO Address(PersonId,City,State) VALUES(1,'Eugene','Oregon');INSERT INTO Address(PersonId,City,State) VALUES(2,'Las Vegas','Nevada');INSERT INTO Address(PersonId,City,State) VALUES(3,'Denver','Colorado');alter table person modify personId int auto_increment; --新增加自增字段

查询：

项目六：删除重复的邮箱(难度：简单)

编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id最小的那个。

+----+---------+

| Id | Email |

+----+---------+

| 1 | a@b.com |

| 2 | c@d.com |

| 3 | a@b.com |

+----+---------+

Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+

| Id | Email |

+----+------------------+

| 1 | a@b.com |

| 2 | c@d.com |

+----+------------------+