MYSQL学习03--MySQL表操作、表连接
1. MySQL表数据类型
- 数值类型
- 日期和时间类型
- 字符串类型
2. 用SQL语句创建表
- 基本语法
CREATE TABLE table_name (column_name column_type); - 创建实例
CREATE TABLE IF NOT EXISTS `courses`( `cid` INT UNSIGNED AUTO_INCREMENT, `student_name` VARCHAR(40) NOT NULL, `course_name` VARCHAR(40) NOT NULL, PRIMARY KEY (`cid`) )ENGINE = INNODB DEFAULT CHARSET= utf8;
3. 用SQL语句向表中添加数据
- 基本语法
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
- 插入实例
INSERT INTO salary(s_name,s_sex,salary) VALUES("张三","m",2500);
4. 用SQL语句删除表
- 基本语法
DROP TABLE table_name ; - 删除表的几种方式的不同
- DROP
DROP TABLE table_name ; - TRUNCATE
truncate table student; - DELETE
delete from student where T_name = "张三";
5. 用SQL语句修改表
-
修改列名
-
修改表中数据
-
删除行
-
删除列
-
新建列
-
新建行
6. MySQL别名
-
INNER JOIN
-
LEFT JOIN
-
CROSS JOIN
-
自连接
-
UNION
-
以上几种方式的区别和联系
07、作业
- 项目三、超过5名学生的课
创建如下所示的courses 表 ，有: student (学生) 和 class (课程)。
CREATE TABLE IF NOT EXISTS `courses`(
`cid` INT UNSIGNED AUTO_INCREMENT,
`student_name` VARCHAR(40) NOT NULL,
`course_name` VARCHAR(40) NOT NULL,
PRIMARY KEY (`cid`)
)ENGINE = INNODB DEFAULT CHARSET= utf8;
插入数据 :
INSERT INTO courses(student_name,course_name) VALUES("张三","math");
INSERT INTO courses(student_name,course_name) VALUES("李四","English");
INSERT INTO courses(student_name,course_name) VALUES("王五","math");
INSERT INTO courses(student_name,course_name) VALUES("赵六","Biology");
INSERT INTO courses(student_name,course_name) VALUES("五天","math");
INSERT INTO courses(student_name,course_name) VALUES("刘八","math");
INSERT INTO courses(student_name,course_name) VALUES("孙慧","math");
INSERT INTO courses(student_name,course_name) VALUES("王伟","Computer");
INSERT INTO courses(student_name,course_name) VALUES("郑一","math");
INSERT INTO courses(student_name,course_name) VALUES("陈二","math");
INSERT INTO courses(student_name,course_name) VALUES("张一","Computer");
INSERT INTO courses(student_name,course_name) VALUES("张七","math");
INSERT INTO courses(student_name,course_name) VALUES("周天","English");
编写一个 SQL 查询，列出所有超过或等于5名学生的课，同一个学生不被重复计算。
SELECT course_name
FROM
(SELECT DISTINCT student_name,course_name
from courses) aaa
GROUP BY course_name
HAVING COUNT(course_name)>5;
结果如图：
- 项目四、交换工资
创建一个 salary表，如下所示，有m=男性 和 f=女性的值 。
CREATE TABLE IF NOT EXISTS `salary`(
`sid` INT UNSIGNED AUTO_INCREMENT,
`s_name` VARCHAR(40) NOT NULL,
`s_sex` VARCHAR(2) NOT NULL,
`salary` INT NOT NULL,
PRIMARY KEY (`sid`)
)ENGINE = INNODB DEFAULT CHARSET= utf8;
插入数据：
INSERT INTO salary(s_name,s_sex,salary) VALUES("张三","m",2500);
INSERT INTO salary(s_name,s_sex,salary) VALUES("李四","f",1500);
INSERT INTO salary(s_name,s_sex,salary) VALUES("王五","m",5500);
INSERT INTO salary(s_name,s_sex,salary) VALUES("赵六","f",500);
INSERT INTO salary(s_name,s_sex,salary) VALUES("吴八","m",3500);
交换所有的 f 和 m 值:
UPDATE salary SET s_sex =IF(s_sex ="m","f","m");
- 项目五、组合两张表
在数据库中创建表1和表2，并各插入三行数据（自己造）：
CREATE TABLE IF NOT EXISTS `person`(
`person_id` INT UNSIGNED AUTO_INCREMENT,
`first_name` VARCHAR(40) NOT NULL,
`last_name` VARCHAR(40) NOT NULL,
PRIMARY KEY(`person_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `address`(
`address_id` INT UNSIGNED AUTO_INCREMENT,
`person_id` INT UNSIGNED NOT NULL,
`city` VARCHAR(40) NOT NULL,
`state` VARCHAR(40) NOT NULL,
PRIMARY KEY(`address_id`),
FOREIGN KEY(`person_id`) REFERENCES person(`person_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
插入数据：
INSERT INTO person(first_name,last_name) VALUES("jack","chen");
INSERT INTO person(first_name,last_name) VALUES("tom","wang");
INSERT INTO person(first_name,last_name) VALUES("cat","zhang");
INSERT INTO person(first_name,last_name) VALUES("joy","chen");
INSERT INTO address(person_id,city,state) VALUES(1,"hubei","wuhan");
INSERT INTO address(person_id,city,state) VALUES(2,"hunan","changsha");
INSERT INTO address(person_id,city,state) VALUES(3,"sanxi","san");
INSERT INTO address(person_id,city,state) VALUES(4,"beijing","canpin");
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State:
SELECT p.person_id,p.last_name,p.first_name,a.city,a.state
from person p
LEFT JOIN address a
on p.person_id =a.person_id;
- 项目六、删除重复的邮箱
编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
建表省略。
DELETE FROM email where ID NOT IN (
SELECT minid FROM(
SELECT MIN(ID) as minid
FROM email
GROUP BY email) b
);
