MySQL学习笔记(二)

MySQL学习笔记(二)

1、MySQL表的数据类型

MySQL数据类型主要有3类：数值、时间和日期、字符串

参考链接：http://www.runoob.com/mysql/mysql-data-types.html

2、用SQL语句创建表

简单的建表操作

CREATE TABLE table_name (column_name column_type);

复杂的建表操作

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

语句解析：

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
• ENGINE 设置存储引擎，CHARSET 设置编码。

3、用SQL语句向表中添加数据

通用语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

插入多行数据：

INSERT INTO table_name  (field1, field2,...fieldN)  
						VALUES  				  (valueA1,valueA2,...valueAN),
(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;

4、用SQL语句删除表

简单的删除表操作：

DROP TABLE table_name;

drop、truncate、delete的区别

1、全部删除 drop

2、保留表、但删除所有记录 truncate

3、删除部分记录 delete

5、用SQL语句修改表

修改列名：

# 语法一
Alter table tbl_name modify[column] col_name [first|after col_name]
# 语法二
alter table tab_name change[column] old_name new_name [first|after col_name]

修改表中的数据

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

删除行

DELETE FROM table_name WHERE cust_id = 123;

删除列

alter table 表名 drop column 列名;

新建行

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

新建列

alter table 表名 add column 列名 varchar(30);

作业一：

建表

CREATE TABLE courses(
   student VARCHAR(100) NOT NULL,
   class VARCHAR(40) NOT NULL);

插入数据

INSERT INTO courses
VALUES
	( 'A', 'Math' ),
	( 'B', 'English' ),
	( 'C', 'Math' ),
	( 'D', 'Biolagy' ),
	( 'E', 'Math' ),
	( 'F', 'Computer' ),
	( 'G', 'Math' ),
	( 'H', 'Math' ),
	( 'I', 'Math' ),
	( 'A', 'Math' );

列出所有超过或等于5名学生的课

SELECT class FROM (SELECT DISTINCT student, class FROM courses) as A GROUP BY class having count(student)>=5;

作业二

创建一个 salary 表，有m=男性 和 f=女性的值 。交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。

建表操作

CREATE TABLE salary(
   id INT NOT NULL PRIMARY KEY,
   name CHAR(8) NOT NULL,
   sex CHAR(8) NOT NULL,
   salary INT NOT NULL);

插入数据

INSERT INTO salary 
VALUES
	(1,'A',‘f’,2500),
	(2,'B',‘m’,1500),
	(3,'C',‘f’,5500),
	(4,'D',‘m’,500),

交换所有的 f 和 m 值

SELECT id,name,CASE sex WHEN 'f' THEN 'm' ELSE 'f' END AS sex,salary FROM salary;

作业三

组合两张表

建表操作：

CREATE TABLE Person(
   PersonID INT NOT NULL PRIMARY KEY,
   FirstName VARCHAR(255) NOT NULL,
   LastName VARCHAR(255) NOT NULL,);

CREATE TABLE Address(
   AddressID INT NOT NULL PRIMARY KEY,
   PersonID INT NOT NULL,
   City VARCHAR(255) NOT NULL,
   State VARCHAR(255) NOT NULL,);

插入数据

INSERT INTO Person 
VALUES
	(1,'Alice','Wang'),
	(2,'Mason','Tao'),
	(3,'Jacky','Xu')；

INSERT INTO Address 
VALUES
	(1,2,'Shanghai','Shanghai'),
	(2,3,'Beijing','Beijing'),
	(3,4,'Suzhou','Jiangsu'),

合并两张表

SELECT FirstName,LastName,City,State
From Person LEFY JOIN Address
ON Person.PersonID = Address.PersonID

作业四

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

建表

CREATE TABLE salary(
   ID INT NOT NULL PRIMARY KEY,
   Email varchar(255) NOT NULL,);

插入数据

INSERT INTO Address 
VALUES
	(1,'a@b.com'),
	(2,'c@b.com'),
	(3,'a@b.com'),

删除重复的邮箱

DELETE e2 FROM email e1 JOIN email e2
ON e2.Email = e1.Email WHERE e2.Id > e1.Id;