MySQL数据库基本操作
一、初识SQL语句
SQL(Structured Query Language 即结构化查询语言)
SQL语句主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型:
DDL语句 数据库定义语言:数据库、表、视图、索引、存储过程,例如CREATE DROP ALTER
DML语句 数据库操纵语言:插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
DCL语句 数据库控制语言:例如控制用户的访问权限GRANT、REVOKE
二、系数据库
**information_schema:虚拟库,主要存储了系统中的一些数据库对象的信息,例如用户表信息、列信息、权限信息、字符信息等performance_schema:**主要存储数据库服务器的性能参数
mysql: 授权库,主要存储系统用户的权限信息
sys: 主要存储数据库服务器的性能参数
创建需要的业务主库…
三、创建业务数据库
语法:
CREATE DATABASE 数据库名;
数据库命名规则
区分大小写
唯一性
不能使用关键字如:create select
不能单独使用数字
查看数据库
SHOW DATABASES;
选择数据库
SELECT databases
USE 数据库名
删除数据库
DROP DATABASE 数据库名
MySQL数据库类型
一、MySQL常见的数据类型
在MySQL数据库管理系统中,可以通过存储引擎来决定表的类型。同时,MySQL数据库管理系统也提供了数据类型决定表存储数据的类型。MySQL数据库管理系统提供的数据类型:
数值类型:
整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT
浮点数类型 FLOAT DOUBLE
定点数类型 DEC
位类型 BIT
字符串类型:
CHAR系列 CHAR VARCHAR
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
枚举类型: ENUM
集合类型: SET
时间和日期类型: DATE TIME DATETIME TIMESTAMP YEAR
二、数据类型测试
整型类型测试:tinyint,int
作用:用于存储用户的年龄、游戏的level、经验值等。
LAB1:
mysql> create table test1(
->tinyint_test tinyint,
->int_test int
->);
mysql> insert into test1values (111,111);
Query OK, 1 row affected (0.09 sec)
mysql> insert into test1(tinyint_test) values(128);
ERROR 1264 (22003):Out of range value for column 'tinyint_test' at row 1
mysql> insert into test1(int_test) values(2147483647);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'int_test' at row 1
测试结果,默认有符号,超过存储范围出错
测试整型类型的显示宽度
mysql> create table test2(
->id1 int(8),
->id2 int(4)
->);
mysql> insert into test2 values(3,2147483647);
Query OK, 1 row affected (0.05 sec)
当插入大于宽度限制的值,仍然可以存储
整型宽度仅为显示宽度,不是限制,因此建议整型无需指定宽度
浮点数类型测试
作用:用于存储用户的身高、体重、薪水等
浮点数和定点数都可以用类型名称后加(M,D)的方式来表示,(M,D)表示一共显示M位数字(整数位+小数位),其中D位于小数点后面,M和D又称为精度和标度。
float和double在不指定精度时,默认会按照实际的精度(由实际的硬件和操作系统决定)来显示,而decimal在不指定精度时,默认的整数位为10,默认的小数位为0
定点数在MySQL内部以字符串形式存储,比浮点数更精确,适合用来表示货币等精度高的数据。
mysql> create table test4(float_test float(5,2));//一共5位,小数占2位
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(10.2),(70.125),(70.246);
Query OK, 3 rows affected (0.00 sec)
Records:3 Duplicates:0 Warnings:0
mysql> desc test4;
+--------- - +
| float_test |
+ ---------- +
| 10.20|
| 70.12|
| 70.25|
+ ---------- +
3 rows in set (0.00 sec)
整数位超过了规定的数值会报错,无法添加,小数位超过了规定的数值会通过五舍六入自动计算,不会报错。
位类型测试:BIT
BIT(M)可以用来存放多位二进制数,M范围从1~64,如果不写默认为1位对于位字段可以使用函数读取:
bin()显示为二进制
hex()显示为十六进制
mysql> create table test_bit (id bit(4));
Query OK, 0 rows affected (0.35 sec)
mysql> insert into test_bit values(4);
mysql> select * from test_bit;
+ ------- +
| id|
+ ------- +
| |
+ ------- +
1 row in set (0.00 sec)
mysql> select bin(id),hex(id) from test_bit;
+ ------- + ------- +
| bin(id) | hex(id) |
+ ------- + ------- +
| 100 | 4 |
+ ------- + ------- +
1 row in set (0.00 sec)
插入的数值不能超过指定宽度的数值,不然会显示警告或者是报错。
时间和日期类型测试: year、date、time、datetime、timestamp
作用: 用于存储用户的注册时间,文章的发布时间,文章的更新时间,员工的入职时间等
mysql> create table test_time(
-> d date,
-> t time,
-> dt datetime);
Query OK, 0 rows affected (0.35 sec)
mysql> insert into test_time values(now(),now(),now());
Query OK, 1 rows affected,1 warning (0.00 sec)
mysql> select * from test_time;
+ ---------- + -------- + ------------------ +
| d | t | dt |
+ ---------- + -------- + ------------------ +
| 2022-09-12 | 11:45:12 |2022-09-12 11:45:12 |
+ ---------- + -------- + ------------------ +
1 row in set (0.00 sec)
timestamp类型插入的是null值时,显示的是当前时间
注意事项:
其他时间,按要求插入
=插入年份是,尽量使用四位数
=插入两位数年份是,<=69的,以20开头,结果是2069
>=70的,以19开头,结果是1970
字符串类型测试: CHAR、VARCHAR
作用: 用于存储用户的姓名、爱好、发布的文章等
CHAR 列的长度固定为创建表时声明的长度:0~255
VARCHAR 列中的值为可变长字符串,长度:0~65535
注:在检索的时候,CHAR列删除了尾部的空格,而VARCHAR则保留这些空格
mysql> create table vc (
-> v varchar(4),
-> c char(4)
->);
Query OK, 0 rows affected (0.03 sec)
查看空格可以使用
mysql> select concat(v,'='),concat(c,'=') from vc;
或者使用length长度查看
mysql> select length(v),lengtht(c) from vc;
字符串类型
ENUM类型即枚举类型、集合类型SET测试
字段的值只能在给定范围中选择
常见的是单选按钮和复选框
enum 单选 只能在给定的范围内选一个值,如性别sex男male/女femaleset 多选 在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3…)表schopl.student3
姓名 name varchar(50)
性别 sex enum(‘m’,'f)
爱好 hobbyset(‘music’, ‘book’,‘game’,‘disc’)
mysql> use school
mysql> create table student3(
-> name varchar(50),
->sex enum('m','f),
->hobby set('music','book','game','disc')
->);
Ouerv OK.0 rows affected (0.31Sec)
创建出来的内容在添加上新的数据时只能从hobby中所填写的内容选择,若是添加了不属于hobby中的数据,会显示报错。
mysql> insert into student3 values('josk','m','box');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
MySQL表操作 DDL
表是数据库存储数据的基本单位,由若干个字段组成,主要用来存储数据记录。表的操作包括创建表、查看表、修改表和删除表。
这些操作都是数据库管理中最基本,也是最重要的操作。本节内容包括:
创建表 create table
查看表结构 desc table,show create table
表完整性约束
修改表 alter table
复制表 create table…
删除表drop table
一、创建表(表的基本操作)
表school.student1
字段 字段 字段
id name sex age
1 tom male 23 记录
2 jack male 21 记录
3 alice female 19 记录
语法:
create table 表名(
字段名1 类型[(宽度)约束条件],
字段名2 类型[(宽度)约束条件],
字段名3 类型[(宽度)约束条件]
)[存储引擎,字符集];
在同一张表中,字段名是不能相同的
宽度和约束条件可以选择是否添加上
字段名和类型是必须要有的
mysql>create database school;
mysql>use school;
mysql>create table student1(
->id int,
->name varchar(50),
->sex enum('m','f'),
->age int);
Query OK,0 rows affected (0.09 sec)
- 向表中插入内容
语法:
insert into 表名(字段1,字段2……)values(字段值列表……);
- 查询表中内容
语法:
select * from 表名; // 查询表中所有字段的值
select 字段1,字段2…… from 表名; //查询表中指定字段的值
二、查看表结构
DESCRIBE查看表结构
DESCRIBE 表名;
DESC 表名;
SHOW CREATE TABLE 查看表详细结构
SHOW CREATE TABLE 表名;
三、表完整性约束
作用:用于保证数据的完整性和一致性
约束条件 | 说明 |
---|---|
PRIMARY KEY (PK) | 标识该字段为该表的主键,可以唯一的标识记录,不可以为空 UNIQUE+NOT NULL |
FOREIGN KEY (FK) | 标识该字段为该表的外键,实现表与表(父表主键/子表1外键/子表2外键)之间的关联 |
NOT NULL | 标识该字段不能为空(空是没有插入值,并不是空白的意思) |
UNIQUE KEY (UK) | 标识该字段的值是唯一的,可以为空,一个表中可以有多个UNIQUE KEY |
AUTO_INCREMENT | 标识该字段的值自动增长(整数类型,而且为主键) |
DEFAULT | 为该字段设置默认值 |
UNSIGNED | 无符号,正数 |
ZEROFILL | 使用0填充I,例如0000001 |
说明:
1.是否允许为空,默认NULL,可设置NOTNULL,字段不允许为空,必须赋值
2.字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值
sex enum('male','female') not null default 'male' //两个默认值,非空和默认male
age int unsigned NOT NULL default 20 //必须为正值(无符号)不允许为空 默认是20
3.是否是key
主键 primary key 区分唯一性的标识
外键forengn key 关联表与表之间的联系,外键是主键的子表
索引(index,unique ……)
not mull 非空
default 设置默认值
添加数据时,若是没有插入数据,会默认插入默认值
mysql>create table student2(
->id int not null,
->name varchar(50) not null,
->sex enum('m','f') default 'm' not null,
->age int unsigned default 18 not null,
->hobby set('disc','book','music') default 'disc,book' not null);
Query OK,0 row affected (0.06 sec)
unique 为约束唯一,作为约束值时,插入的表内的数据必须唯一,否则添加不进去,数据报错。
mysql>create table test1(
->t_id int,
->t_name varchar(50) unique,
->t_comment varchar(100));
Query OK,0 row affected (0.08 sec)
mysql>create table test2(
->t_id int,
->t_name varchar(50),
->t_comment varchar(100)
->unique (t_name));
Query OK,0 row affected (0.06 sec)
设置主键,字段的值是不允许重复,且不允许为空(unique+not null)
单列做主键
多列做主键(复合主键)
mysql>create table test3(
->t_id int primary key not null auto_increment,
->t_name varchar(50) unique,
->t_sex enum('male','female') not null default 'male',
->t_age int not null default 20 );
Query OK,0 row affected (0.03 sec)
mysql>create table test4(
->t_id int not null auto_increment,
->t_name varchar(50) unique,
->t_sex enum('male','female') not null default 'male',
->t_age int not null default 20,
->primary key(t_id));
Query OK,0 row affected (0.01 sec)
复合主键
表school.service
host_ip | 存储主机IP |
---|---|
service name | 服务名 |
port | 服务对应的端口 |
allow(Y,N) | 服务是否允许访问 |
主键:host_ip + port= primary key
一个管不了事,只选一个会出现重复的现象,而主键只能是唯一值,不能出现重复现象,所以这个时候需要另外一个进行组合,这样主键才不会出现重复现在,而这时的主键也是唯一值,这便是复合主键。
mysql> create table service(
->host_ip varchar(15) not null,
->service_name varchar(10) not null,
->port varchar(5) not null,
->allow enum('Y','N') default 'N',
->primary key(host_ip,port));
Query OK, 0 rows affected (0.00 sec)
设置外键约束
设置外键约束 FOREIGN KEY
//父表company.employees
mysql> create table employees(
-> name varchar(50) not null,
->mail varchar(20), //主键,主键和外键的类型和约束必须是一样的
-> primary key(name)
->)engine=innodb;
Query OK,0 row affected (0.03 sec)
//子表company.payroll
mysql> create table payroll( I
-> id int not null auto_increment,
-> name varchar(50) not null, //外键,主键和外键的类型和约束必须是一样的
->payroll float(10,2) not null,
->primary key(id),
->foreign key(name) references employees(name) on update cascade on delete cascade )engine=innodb;
Query OK,0 row affected (0.10 sec)
子表name外键,关联父表(employees主键name),同步更新 同步删除
在创建父表和子表时要注意顺序,只有创建父表了才能创建子表,因为子表要关联父表的主键。
结论:
当父表中某个员工的记录修改时,子表也会同步修改;
当父表中删除某个员工的记录时,子表也会同步删除。
四、修改表 ALTER TABLE
语法:
1.修改表名
ALTER TABLE 表名
RENAME 新表名;
2.增加字段
ALTER TABLE 表名
ADD 字段名 数据类型[完整性约束条件…],
ADD 字段名数据类型[完整性约束条件…];
ALTER TABLE 表名
ADD 字段名数据类型 [完整性约束条件…]FIRST;
ALTER TABLE 表名
ADD 字段名数据类型 [完整性约束条件…]AFTER 字段名;
3.删除字段
ALTER TABLE 表名
DROP 字段名;
4.修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件……]
ALTER TABLE 表名
CHANGE 旧字段名 型字段名 旧字段类型 [完整性约束条件……]
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件……]
示例:
1.修改存储引擎
mysql> alter table service
-> engine=innodb; //engine=myisam|memoryll....
2.添加字段
mysql> create table student10 (id int);
mysql> alter table student10
-> add name varchar(20) not null,
-> add age int not null default 22;
mysql> alter table student10
-> add stu_num int not null after name; //添加name字段之后
mysql> alter table student10
-> add sex enum('male','female') default 'male' first; //添加到最前面
3.删除字段
mysql> alter table student10
-> drop sex;
4.修改字段类型modify
mysql> alter table student10
-> modify age tinyint not null default 22; //注意保留原有的约束条件
mysql> alter table student10
-> modify id int not null primary key; I //修改字段类型、约束、主键
5.增加约束(针对已有的主键增加auto_increment)
mysql> alter table student10 modify id int not null ~~primary key~~ auto_increment; //错误,该字段已经是primary key ERROR 1068 (42000): Multiple primary key defined
mysql> alter table student10 modify id int not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records:0 Duplicates:0 Warnings:0
6.增加复合主键
mysql> alter table service2
-> add primary key(host_ip,port);
7.增加主键
mysql> alter table student1
-> add primary key(id);
8.增加主键和自动增长
mysql> alter table student1
-> modify id int not null primary key auto_increment;
9.删除主键[primary key auto_increment]
a.删除自增约束
mysql> alter table student10 modify id int not null;
b.删除主键
mysql> alter table student10
-> drop primary key;
先删除自增再删除主键,自增依赖于主键,若是直接删除主键自增那边会抗议。
五、复制表
复制表结构+记录(key不会复制:主键、外键和索引)
mysql> create table new_service select * from service;
只复制表结构
mysql> create table new1_service select *from service where 1=2; //条件为假,查不到任何记录复制表结构,包括Key
mysql> create table t4 like employees;
六、删除表
DROP TABLE 表名;
MySQL数据操作:DML
在MySQL管理软件中,可以通过SQL语句中的DML语言来实现数据的操作,包括使用INSERT实现数据的插入、DELETE实现数据的删除以及UPDATE实现数据的更新。
更新数据insert
更新数据update
删除数据delete
一、插入数据INSERT
1.插入完整数据(顺序插入)
语法—:
INSERT INTO-表名(字段1,字段2,字段3…字段A)VALUES(值1,值2,值3…值n)
语法二:
INSERT INTO 表名VALUES(值1,值2,值3…值n);
2.指定字段插入数据
语法:
INSERT INTO 表名(字段2,字段3…) VALUES(值2,值3…);
3.插入多条数据
语法:
INSERT INTO 表名(字段2,字段3…) VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4.插入查询结果
语法:
INSERT INTO 表1 VALUES(字段1,字段2,字段3……字段n)
SELECT (字段1,字段2,字段3……字段n) from 表名2
WHERE ……;
mysql> insert into test5
->select * from test6 where age>6;
Query OK, 3 row affected (0.01 sec)
Records:3 Duplicates:0 Warnings:0
二、更新数据UPDATE
语法:
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
示例:
mysql>select user,host,authentication_string from mysql.user;
+ -------- + -------- + --------------------------------------- +
| user | host | authentication_string |
+ -------- + --------- + -------------------------------------- +
| root |localhost |*26194D7EF5057F123665C1AF7D9EBEDB0015E4EA|
| mysql.sys|localhosl |*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE|
+ ---------+ -------- + --------------------------------------- +
2 rows in set (0.00 sec)
mysql> update mysql.user set
-> authentication_string=password('gkgfjhfh520^&*')
-> where user='root'and host='localhost';
Query0K, 0 rowsaffected, 1 warning (0.00 sec)
Rows matched:1 Changed:0 Warnings:1
mysql> flush privileges;
Query 0K, 0 rows affected(0.00 sec)
三、删除数据DELECT
语法:
DELECT FROM 表名
WHERE CONITION;
示例:
mysql>delect from mysql.user where authentication_string=' ';
MySQL单表查询SELECT :DQL
简单查询
通过条件查询
查询排序
限制查询记录
使用集合函数查询
分组查询
使用正则表达式查询
mysql>CREATE TABLE company.employee5(
->id int primary key AUTO_INCREMENT not null,
->name varchar(30) not null,
->sex enum('male','female') default 'male' not null,
->hire_date date not null,
->post varchar(50) not null,
->job_description varchar(100),
->salary double(15,2) not null,
->office int,
->dep_id int);
一、简单查询
简单查询
语法:
SELECT * FROM 表名;
SELECT (字段1,字段2,……字段n)FROM 表名;
避免重复DISTINCT
SELECT post FROM 表名;
SELECT DISTINCT 字段1 FROM 表名
注:不能部分使用DISTINCT,通常仅用于某一字段。
通过四则运算查询
SELECT name, salary, salary*14 FROM employee5;
SELECT name,salary,salary*14 AS Annual_salary FROM employee5;//AS Annual_salary 给salary*14起一个别名,年度薪水
SELECT name, salary, salary*14 Annual_salary FROM employee5;
字段后面空格或是空格加上AS可以给字段起一个别名来代替字段显示在数据表中。
定义显示格式
CONCAT() //函数用于连接字符串
SELECT CONCAT(name,' annual salary:' salary*14)AS Annual_salary FROM empl
二、单条件查询
单条件查询
mysql>SELECT name,post FROM employee5 WHERE post='hr';
多条件查询
mysql>select name,salary from employee5 where post="hr' and salary>10000;
关键字BETWEEN AND
//查询employee5表中薪水在5000和15000之间的数据
mysql>SELECT name,salary FROM employee5 WHERE salary between 5000 and 15000;
//查询employee5表中薪水不在5000和15000之间的数据
mysql>SELECT name,salary FROM employee5 WHERE salary not between 5000 and 15000;
关键字IS NULL
//查询employee5表中name,job_description字段中job_description是否设置的空值
mysql>SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
//查询employee5表中name,job_description字段中job_description有职位的
mysql>SELECT name,job_description FROM employee5
WHERE job_description IS NOT NULL;
关键字IN集合查询
//查询employee5表中name,salary字段中的salary薪水在4000或5000或6000或9000的
mysql>SELECT name,salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;
//查询employee5表中name,salary字段中的salary薪水在4000,5000,6000,9000范围内的
mysql>SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;
//查询employee5表中name,salary字段中的salary薪水不在4000,5000,6000,9000范围内的
mysql>SELECT name, salary FROM employee WHERE salary NOT IN (4000,5000,6000,9000);
关键字LIKE模糊查询
通配符‘%’
SELECT* FROM employee5
WHERE name LIKE ‘al%’;
%在这里表示任意多个字符。
通配符’_’
SELECT* FROM employee5
WHERE name LIKE ‘al__’;
明确知道一个字符的时候,用’ _ ‘,这表示一个数据,两个’ __'表示两个数据。
三、查询排序
按单列排序
//ORDER BY排序
mysql>SELECT * FROM employee5 ORDER BY salary;
//升序排序
mysql>SELECT name, salary FROM employee5 ORDER BY salary ASC;
//降序排序
mysql>SELECT name,salary FROM employee5 ORDER BY salary DESC;
按多列排序
//查询employee5表入职降序排序,薪水升序排序
mysql>SELECT* FROM employee5 ORDER BY
->hire_date DESC salary ASC;
四、限制查询的记录数据
示例:
//从一开始选择5个数据,开始数值为0
mysql>SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5; //默认初始位置为0
//从一开始选择5个数据,开始数值为0
mysql>SELECT*FROM employee5 ORDER BY salary DESC LIMIT 0,5;
mysql>SELECT*FROM employee5 ORDER BY salary DESC LIMIT 3,5; //从第4条开始,共显示5条
五、使用集合函数查询
示例:
//数量值
mysql>SELECT COUNT(*) FROM employee5;
//dep_id=101的数值
mysql>SELECT COUNT(*) FROM employee5 WHERE dep_id=101;
//最大值
mysql>SELECT MAX(salary) FROM employee5;
//最小值
mysql>SELECT MIN(salary) FROM employee5;
//平均值
mysql>SELECT AVG(salary) FROM employee5;
//总数值
mysql>SELECT SUM(salary) FROM employee5;
// dep_id=101的总数值
mysql>SELECT SUM(salary) FROM emnlovee5 WHERE dep_id=101.
六、分组查询
分组 GROUP BY
拼在一起 GROUP_CONCAT()
GROUP BY和GROUP_CONCAT()函数一起使用
//只要部门id相同的把他们的名字拼在一起
mysql>SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
//只要部门id相同的把他们的名字都拼在一起,name字段起一个别名叫emp_members
mysql>SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;
GROUP BY也可以和集合函数一起使用
mysql>SELECT dep_id,AVG(salary) FROM employee5 GROUP BY dep_id;
七、使用正则表达式查询
正则表达式:REGEXP
//查询name字段中有ali的数据
mysql>SELECT*FROM employee5 WHERE name REGEXP '^ali';
//查询name字段中以yun结尾的数据
mysql>SELECT*FROM employee5 WHERE name REGEXP 'yun$';
//查询name字段中出现2次m的数据
mysql>SELECT*FROM employee5 WHERE name REGEXP'm(2)';
MySQL多表查询 DQL
多表连接查询
复合条件连接查询
子查询
一、多表连接查询
交叉连接: 生成笛卡尔积,它不使用任何匹配条件
内连接: 只连接匹配的行
外连接之左连接: 会显示左边表内所有的值,不论在右边表内匹不
匹配外连接之右连接: 会显示右边表内所有的值,不论在左边表内
匹不匹配全外连接: 包含左、右两个表的全部行
交叉连接 两个表关联起来,没有任何的匹配条件
mysql>select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6;
内连接:只找出有部门的员工
mysql>select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6
->where employee6.dept_id=department6.dept_id;
外连接语法:
select 字段列表 from 表1 left|right join 表2 on 表1.字段 = 表2.字段;
left join 左边连接,主要以左边为主
right join 右连接,主要以右边为主
//找出所有的员工,以及所属部门,包括没有部门的员工
mysql>select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id;
全外连接
mysql>select * from employee6 full join department6;
二、复合条件连接查询
示例1:以内连接的方式查询employee6和department6表,并且employee6表中的age字段值必须大于25
找出公司所有部门中年龄大于25岁的员工
mysql>SELECT emp_id,emp_name,age,dept_name FROM employee6,department6
->WHERE employee6.dept_id = department6.dept_id AND age >25;
示例2:以内连接的 方式查询employee6和department6表,并且以age字段的升序方式显示
mysql>SELECT emp_id,emp_name,age,dept_name
->FROM employee6,department6
->WHERE employee6.dept_id = department6.dept_id
->order by age asc;
三、子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果,可以为外层查询语句提供查询条件。
子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS和 NOT EXISTS等关键字还可以包含比较运算符:=、!=、>、<等
1.带IN关键字的子查询
查询employee表,但dept_id必须在department表中出现过
mysql>select * from employee6
->where dept_id in(select dept_id from department6);
2.带比较运算符的子查询
=、!=、>、>=、<、<=、<>
查询年龄大于等于25岁员工所在的部门
mysql>select dept_id,dept_name from department6
->where dept_id in
->(select distinct dept_id from employee6 where age>=25);
3.带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录,而是返回一个真假值。
Ture或False,当返回Ture时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询
//department表中存在dept_id=203,Ture
mysql>select * from employee6
->where exists (select * from department6 where dept_id=203);
MySQL索引
创建索引
创建表时创建索引
CREATE在已存在的表上创建索引
ALTER TABLE在已存在的表上创建索引
查看并测试索引
删除索引
一、索引简介
索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。
二、索引的分类
普通索引
唯一索引 干锋
全文索引
单列索引
多列索引
空间索引
三、创建索引
创建表时
语法:
CREATE TABLE 表名(
字段名1 数据类型 [完整性约束条件…]
字段名2 数据类型[完整性约束条件…],
[UNIQUE|FULLTEXT|SPATIAL ] INDEX| KEY
[索引名](字段名[(长度)] [ASC|DESC])
);
创建普通索引示例:
创建索引必须要有index或者是key
可以是unique index/key 唯一索引
fulltext index/key 全文索引
spatial index/key
也可以index/key (字段名)
多列索引:index(字段名1,字段名2……)
创建索引:
mysql>create TABLE department10(
->dept_id INT,
->dept_name VARCHAR(30),
->comment VARCHAR(50),
->index (dept_name));//单列索引
在已存在的表中创建索引
语法:
create [unique|fulltext|spatial] index 索引名 on 表名 (字段名[(长度)] [asc|desc]);
创建普通索引名称
这个时候索引名是必须要添加上的
mysql>create index index_dept_name ondepartment (dept_name);
创建唯一索引示例:
mysql>create unique index index_dept_name ondepartment (dept_name);
创建全文索引示例:
mysql>create fulltext index index_dept_name ondepartment (dept_name);
创建多列索引示例:
mysql>create index index_dept_name ondepartment (dept_name,comment);
四、管理索引
查看索引
show create table 表名\G
explain:命令的作用是查看查询优化器如何决定执行查询
删除索引
show create table employee6;
drop index 索引名 on 表名;
MySQL视图 VIEW
视图简介
创建视图
查看视图
修改视图
通过视图操作基表
删除视图
一、视图简介
MySQL视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。对其中所引用的基础表来说,MySQL视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表,或者其它视图。通过视图进行查询没有任何限制,通过它们进行数据修改时的限制也很少。
视图是存储在数据库中的SQL查询语句,它主要出于两种原因:安全原因,视图可以隐藏一些数据,如:一些敏感的信息,另一原因是可以使复杂的查询易于理解和使用。
二、创建视图
语法一:
CREATE [ALGORITHM=(UNDEFINED| MERGE | TEMPTABLE]
VIEW 视图名[(字段1,字段2…)]
AS SELECT语句
[WITH [CASCADED| LOCAL] CHECK OPTION ];
语法二:
CREATE VIEW视图名
ASSELECT语句
示例1:
mysql>create view u
->as select user,host,authentication_string from mysql.user;
示例2:创建多个表
mysql> create database shop;
Query OK, 1 row affected (0.21 sec)
mysql> use shop
Database changed
mysql> create table product(
->id int unsigned auto_increment primary key not null,
->name varchar(60)not null,
->price double not null);
mysql> insert into product(name,price) values
->('pear',4.3),
->('orange',6.5),
->('apple',5.0));
mysql> create table purchase(
->id int unsigned auto_increment primary key not null,
->name varchar(60)not null,
->quantity int not null default 0,
->gen_time datetime not null);
mysql> insert into purchase(name,quantity,gen_time) values
->('apple',7,now()),
->('pear',4,now());
//产品名字,产品价格,产品数量
mysql> create view purchase_detail
->as select
->product.name, product.price,purchase.quantity,
->product.price*purchase.quantity as total_value from product,purchase
->where product.name =purchase.name;
三、查看视图
1. SHOW TABLES 查看视图名
SHOW TABLES;
2. SHOW TABLE STATUS
示例:查看数据库mysql中视图及所有表详细信息SHOW TABLE STATUS FROM mysql \G
示例:查看数据库mysql中视图名view_user详细信息
SHOW TABLE STATUS FROM mysql LIKE view_user’\G
3. SHOW CREATE VIEW
示例:查看视图定义信息
SHOW CREATE VIEW view_user\G
4. DESCRIBE
示例:查看视图结构 I
DESC view_user;
四、修改视图
方法一:删除后新创建
DROP VIEW view_userI
CREATE VIEW view_user
AS SELECT user,host FROM mysql.user; SELECT *FROM view_user;
方法二:ALTER修改视图
语法:
ALTER VIEW 视图名
AS SELECT语句;
示例:
ALTER VIEW view_user
AS SELECTuser,passwordFROM mysql.user;
五、通过视图操作基表
查询数据SELECT
SELECT * FROM view_user;
更新数据UPDATE
删除数据DELETE
六、删除视图
语法:
DROP VIEW view_name [,view_name]…;
示例:
USE mysql;
DROP VIEW view_user ;
MySQL 触发器 Triggers
触发器简介
创建触发器
查看触发器
删除触发器
触发器案例
一、触发器简介
触发器(trigger)是一个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete,update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。
例如,当学生表中增加了一个学生的信息时,学生的总数就应该同时改变。因此可以针对学生表创建一个触发器,每次增加一个学生记录时,就执行一次学生总数的计算操作,从而保证学生总数与记录数的一致性。
二、创建触发器
语法:
CREATE TRIGGER 触发器名称 BEFORE|AFTER触发事件ON 表名 FOR EACH ROW
BEGIN
触发器程序体;
END
1创建表
mysql>create table student(
-> id int unsigned auto_increment primary key not null,
-> name varchar(50));
mysql> insert into student(name) values(jack);
mysql> create table student_total(total int);
mysql> insert into student_total values(1);
2.创建触发器student_insert_trigger
mysql> delimiter $$ //$$是结束符
mysql> create trigger student_insert_trigger after insert
->on student for each row
->BEGIN
->update student_total set total=total+1;
->END$$
mysql> delimiter;//换回;是结束符
3.创建触发器student_delete_trigger
mysql> delimiter $$
mysql> create trigger student_delete_trigger after delete
->on student for each row
->BEGIN
->update student_total set total=total-1;
->END$$
mysql> delimiter;//换回;是结束符
触发器案例:
//创建表tab1
mysql>DROP TABLE IF EXISTS tab1;
mysql>CREATE TABLE tab1(
->id int primary key auto_increment,
->name varchar(50),
->sex enum('m',f),
->age int);
//创建表tab2
mysql>DROP TABLE IF EXISTS tab2;
mysql>CREATE TABLE tab2(
->id int primary key auto_increment,
->name varchar(50),
->salary double(10,2));
触发器tab1_after_delete trigger
作用:tab1表删除记录后,自动将tab2表中对应记录删除
mysql> \d $$
mysql> create trigger tab1_after_delete_trigger
->after delete on tab1
->for each row
->BEGIN
->delete from tab2 where name=old.name;
->END$$
触发器tab1_after update trigger作用:当tab1更新后,自动更新tab2
mysql> create trigger tab1_after_update_trigger
->after update on tab1
->for each row
->BEGIN
->insert into tab2(name,salary) values(new.name,5000);
->END$$
更新和删除必须通过主键进行操作