1.1 数据库操作
* 创建数据库:
* create database 数据库名称;
* 创建数据库,判断不存在,再创建:
* create database if not exists 数据库名称;
* 创建数据库,并指定字符集
* create database 数据库名称 character set 字符集名;
-------------------------------------------------------------------------
* 查询所有数据库的名称:
* show databases;
* 查询某个数据库的字符集:查询某个数据库的创建语句
* show create database 数据库名称;
-------------------------------------------------------------------------
*修改数据库的字符集
* alter database 数据库名称 character set 字符集名称;
-------------------------------------------------------------------------
* 删除数据库
* drop database 数据库名称;
* 判断数据库存在,存在再删除
* drop database if exists 数据库名称;
-------------------------------------------------------------------------
* 查询当前正在使用的数据库名称
* select database();
* 使用数据库
* use 数据库名称;
```
#### 课堂练习
-- 创建db_demo数据库,设置数据库字符集为utf8;
CREATE DATABASE db_demo CHARACTER SET utf8;
1.2 数据表操作
* 表的创建 * create table 表名( 列名1 数据类型1 【字段约束】, 列名2 数据类型2 【字段约束】, .... 列名n 数据类型n 【字段约束】 ); * 注意:最后一列,不需要加逗号(,) ------------------------------------------------------------------------- * 查询当前数据库中所有的表 * show tables; * 查询某个数据库中所有的表 * show tables from 数据库名称; * 查询表结构 * desc 表名; ----------------- -------------------------------------------------------- * 修改表名 alter v. 改变,改动;(使)变化;改(服装);改建(某建筑); * alter table 表名 rename to 新的表名; * 修改表的字符集 * alter table 表名 character set 字符集名称; * 添加一列 * alter table 表名 add 列名 数据类型; * 修改列名称 类型 * alter table 表名 change 列名 新列别 新数据类型; * alter table 表名 modify 列名 新数据类型; * 删除列 * alter table 表名 drop 列名; ------------------------------------------------------------------------- * 删除表 * drop table 表名; * drop table if exists 表名;
1.3 MySQL常用数据类型
1.int:整数类型 age int, 2. double:小数类型 score double(5,2), 3. date:日期,只包含年月日,yyyy-MM-dd 4. datetime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss 5. timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss 如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值 6. varchar:字符串 name varchar(20):姓名最大20个字符 zhangsan 8个字符 张三 2个字符
课堂练习
使用db_demo数据库
-
创建表dept1
字段名 | 是否为空 | 数据类型 |
---|---|---|
id | int(7) | |
name | varchar(25) |
CREATE TABLE dept1( id INT(7), NAME VARCHAR(25) );
-
创建表emp5
字段名 | 是否为空 | 数据类型 |
---|---|---|
id | int(7) | |
frist_name | varchar(25) | |
last_name | varchar(25) | |
dept_id | int(7) |
CREATE TABLE emp5( id INT(7), frist_name VARCHAR(25), last_name VARCHAR(25), dept_id INT(7) );
1.4 约束
* 概念: 对表中的数据进行限定,保证数据的正确性、有效性和完整性。 * 分类: 1. 主键约束:primary key 2. 非空约束:not null 3. 唯一约束:unique 4. 外键约束:foreign key ------------------------------------------------------------------------- * 非空约束:not null,某一列的值不能为null 1. 创建表时添加约束 CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name为非空 ); 2. 创建表完后,添加非空约束 ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL; 3. 删除name的非空约束 ALTER TABLE stu MODIFY NAME VARCHAR(20); ------------------------------------------------------------------------- * 唯一约束:unique,某一列的值不能重复 1. 注意: * 唯一约束可以有NULL值,但是只能有一条记录为null 2. 在创建表时,添加唯一约束 CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE -- 手机号 ); 3. 删除唯一约束 ALTER TABLE stu DROP INDEX phone_number; 4. 在表创建完后,添加唯一约束 ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE; ------------------------------------------------------------------------- * 主键约束:primary key。 1. 注意: 1. 含义:非空且唯一 2. 一张表只能有一个字段为主键 3. 主键就是表中记录的唯一标识 2. 在创建表时,添加主键约束 create table stu( id int primary key,-- 给id添加主键约束 name varchar(20) ); 3. 删除主键 -- 错误 alter table stu modify id int ; ALTER TABLE stu DROP PRIMARY KEY; 4. 创建完表后,添加主键 ALTER TABLE stu MODIFY id INT PRIMARY KEY; 5. 自动增长: 1. 概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长 2. 在创建表时,添加主键约束,并且完成主键自增长 create table stu( id int primary key auto_increment,-- 给id添加主键约束 name varchar(20) ); 3. 删除自动增长 ALTER TABLE stu MODIFY id INT; 4. 添加自动增长 ALTER TABLE stu MODIFY id INT AUTO_INCREMENT; ------------------------------------------------------------------------- * 外键约束:foreign key,让表于表产生关系,从而保证数据的正确性。 1. 在创建表时,可以添加外键 * 语法: create table 表名( .... 外键列 constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) ); 2. 删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; 3. 创建表之后,添加外键 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称); 4. 级联操作 1. 添加级联操作 语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ; 2. 分类: 1. 级联更新:ON UPDATE CASCADE 2. 级联删除:ON DELETE CASCADE
课堂练习
-
将表departments中的数据插入新表dept2中
CREATE TABLE dept02
AS
SELECT *
FROM myemployees.departments; -
根据表employees创建employees2、
CREATE TABLE myemployees2
AS
SELECT *
FROM myemployees.departments;
SHOW TABLES FROM db_demo;
-
-
删除表emp5
DROP TABLE IF EXISTS emp5;
SHOW TABLES FROM db_demo;
ROLLBACK;
SHOW TABLES FROM db_demo;
DDL删除后数据表无法回滚,删除是永久操作
4.向表dept2的id列中添加PRIMARY KEY约束(my_dept_id_pk)
SHOW TABLES FROM db_demo;
ALTER TABLE dept02 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE dept02 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);
5.向表employees2中定义FOREIGNKEY约束,与之相关联的列是dept2表中的id列
思考:主键与唯一键的区别?
①、一个表中可以有多个唯一索引, 但是只能有一个主键。
②、 主键一定是唯一性索引,唯一性索引并不一定就是主键
主键 不允许为空, 唯一键允许为空,空值不受唯一约束,也就是说可以有多个空值。
注:可以多列组合成一个唯一索引或者一个主键,即组合索引或组合主键
主要的关键字有auto increment自增 comment 对行进行注释,(这里的注释在数据库里可以看到)
2 DML
1. 添加数据: * 语法: * insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n); * 注意: 1. 列名和值要一一对应。 2. 如果表名后,不定义列名,则默认给所有列添加值 insert into 表名 values(值1,值2,...值n); 3. 除了数字类型,其他类型需要使用引号(单双都可以)引起来 ------------------------------------------------------------------------- 2. 删除数据: * 语法: * delete from 表名 [where 条件] * 注意: 1. 如果不加条件,则删除表中所有记录。 2. 如果要删除所有记录 1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作 2. TRUNCATE TABLE 表名; -- 推荐使用,效率更高 先删除表,然后再创建一张一样的表。 * 面试题:delete与TRUNCATE区别? 1.truncate不能加where条件,而delete可以加where条件 2.truncate的效率高 3.truncate 删除带自增长的列的表后,如果再插入数据,数据从1开始 4.delete 删除带自增长列的表后,如果再插入数据,数据从上一次的断点处开始 5.truncate删除不能回滚,delete删除可以回滚 ------------------------------------------------------------------------- 3. 修改数据: * 语法: * 修改单表: update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件]; * 修改多表: update 表1 别名1,表2 别名2 set 字段=新值,字段=新值 where 连接条件 and 筛选条件 * 注意: 1. 如果不加任何条件,则会将表中所有记录全部修改
课堂练习
1.运行以下脚本创建表my_employees
id | first_name | last_name | userid | salary |
---|---|---|---|---|
1 | patel | Ralph | Rpatel | 895 |
2 | Dancs | Betty | Bdancs | 860 |
3 | Biri | Ben | Bbiri | 1100 |
4 | Newman | Chad | Cnewman | 750 |
USE db_demo;
CREATE TABLE my_employees(
id INT ( 10 ),
first_name VARCHAR ( 10 ),
last_name VARCHAR ( 10 ),
userid VARCHAR ( 10 ),
salary DOUBLE ( 10 , 2 )
);
INSERT INTO my_employees(id,first_name ,last_name ,userid ,salary)
VALUES
(1,'patel ','Ralph','Rpatel',895),
(2,'Dancs ','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750);
-
向 users 表中插入数据
id | userid | department_id |
---|---|---|
1 | Rpatel | 10 |
2 | Bdancs | 10 |
3 | Bbiri | 20 |
4 | Cnewman | 30 |
5 | Aropebur | 40 |
USE db_demo;
CREATE TABLE users(
id INT ,
userid VARCHAR ( 10 ),
department_id INT
);
INSERT INTO users(id,userid,department_id)
VALUES
(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Biri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);
-
将 3 号员工的 last_name 修改为“drelxer”
UPDATE my_employees
SET last_name = 'drelxer'
WHERE id = 3;
修改前
修改后
-
将所有工资少于 900 的员工的工资修改为 1000
UPDATE my_employees
SET salary = 1000
WHERE salary < 900;
-
将 userid 为 Bbiri 的 user 表和 my_employees 表的记录全部删除
DELETE m,u
FROM my_employees m JOIN users u
ON m.userid = u.userid
WHERE m.userid = 'Bbiri';
-
删除所有数据
DELETE FROM my_employees;
DELETE FROM users;
-
检查所作的修正
SELECT * FROM my_employees;
SELECT * FROM users;
-
清空表 my_employees
TRUNCATE TABLE my_employees;
4 数据库设计
1. 多表之间的关系 1. 分类: 1. 一对一(了解): * 如:人和身份证 * 分析:一个人只有一个身份证,一个身份证只能对应一个人 2. 一对多(多对一): * 如:部门和员工 * 分析:一个部门有多个员工,一个员工只能对应一个部门 3. 多对多: * 如:学生和课程 * 分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择 2. 实现关系: 1. 一对多(多对一): * 如:部门和员工 * 实现方式:在多的一方建立外键,指向一的一方的主键。 2. 多对多: * 如:学生和课程 * 实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键 3. 一对一(了解): * 如:人和身份证 * 实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。 3. 案例 -- 创建旅游线路分类表 tab_category -- cid 旅游线路分类主键,自动增长 -- cname 旅游线路分类名称非空,唯一,字符串 100 CREATE TABLE tab_category ( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(100) NOT NULL UNIQUE ); -- 创建旅游线路表 tab_route /* rid 旅游线路主键,自动增长 rname 旅游线路名称非空,唯一,字符串 100 price 价格 rdate 上架时间,日期类型 cid 外键,所属分类 */ CREATE TABLE tab_route( rid INT PRIMARY KEY AUTO_INCREMENT, rname VARCHAR(100) NOT NULL UNIQUE, price DOUBLE, rdate DATE, cid INT, FOREIGN KEY (cid) REFERENCES tab_category(cid) ); /*创建用户表 tab_user uid 用户主键,自增长 username 用户名长度 100,唯一,非空 password 密码长度 30,非空 name 真实姓名长度 100 birthday 生日 sex 性别,定长字符串 1 telephone 手机号,字符串 11 email 邮箱,字符串长度 100 */ CREATE TABLE tab_user ( uid INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(100) UNIQUE NOT NULL, PASSWORD VARCHAR(30) NOT NULL, NAME VARCHAR(100), birthday DATE, sex CHAR(1) DEFAULT '男', telephone VARCHAR(11), email VARCHAR(100) ); /* 创建收藏表 tab_favorite rid 旅游线路 id,外键 date 收藏时间 uid 用户 id,外键 rid 和 uid 不能重复,设置复合主键,同一个用户不能收藏同一个线路两次 */ CREATE TABLE tab_favorite ( rid INT, -- 线路id DATE DATETIME, uid INT, -- 用户id -- 创建复合主键 PRIMARY KEY(rid,uid), -- 联合主键 FOREIGN KEY (rid) REFERENCES tab_route(rid), FOREIGN KEY(uid) REFERENCES tab_user(uid) );