11.MySQL
一、MySQL简介
数据库的安装
1、安装路径不要有中文(重要),不要有空格
2、MySQL默认端口是3306,不要修改。
3、设置MySQL的编码集(采用UTF-8的编码)
4、要把黑窗口的勾勾选上。
数据库的卸载
找到MySQL的安装路径,找到my.ini的配置文件。
basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.5/" 安装的路径
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/" MySQL存储数据的路径
1、通过控制面板卸载MySQL
2、找到上面的两个路径,删除文件夹
二、MySQL管理
三、SQL语句
1、DDL(管理数据库)
1 Create database mydb1; 2 Create database mydb2 character set gbk; 3 Create database mydb3 character set gbk COLLATE gbk_chinese_ci;
1 show databases; // 查看当前数据库服务器中的所有数据库 2 Show create database mydb2; // 查看前面创建的 mydb2数据库的定义信息 3 Drop database mydb3; // 删除前面创建的 mydb3数据库
alter database mydb2 character set utf8; // 查看服务器中的数据库,并把 mydb2 的字符集修改为 utf8
drop database mydb3;
select database();
use mydb2;
1 create table 表名 ( 2 字段 1 字段类型 , 3 字段 2 字段类型 , 4 ... 5 字段 n 字段类型 6 );
SHOW TABLES;
DESC employee;
常用数据类型
TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE
BIT
2、DML 操作 (重要 )
[client]
port=3306
[mysql]
default-character-set=gbk,修改完要重启服务
truncate 和 delete的区别:
(1)truncate删除数据,先删除整个表。再创建一个新的空的表。(效率)
(2)delete删除数据,一条一条删除的。(*****)
(3)事物(insert update delete)
3、DQL(重要,查询) 12种
SELECT * FROM 表名 ;
(2)查询指定列
SELECT id,NAME,gender FROM student;
(3)查询时给字段添加别名( AS )
1 SELECT NAME AS '姓名',sex AS '性别' FROM student; 2 SELECT NAME '姓名',sex '性别' FROM student; // 也可省略AS
(4)查询时添加常量列
SELECT id,NAME,gender,age,'你好' AS 'hello' FROM student; // as设置别名
(5)查询时合并列
SELECT id,NAME,(servlet+jsp) AS '总成绩' FROM student;
(6)查询时去除重复记录(DISTINCT)
SELECT DISTINCT gender FROM student;
(7)条件查询
条件查询(where)
1.逻辑条件: and(与) or(或)
SELECT * FROM student WHERE id=2
2. 比较条件: > < >= <= = <>(不等于) between and (等价于>= 且 <=)
SELECT * FROM student WHERE servlet>70;
3. 判空条件(null 空字符串): is null / is not null / ='' / <>''
1 SELECT * FROM student WHERE address IS NULL 2 SELECT * FROM student WHERE address='';
4.模糊条件: like
% : 表示任意个字符
_ : 表示一个字符
SELECT * FROM student WHERE NAME LIKE '李%';
(8)聚合查询
常用的聚合函数: sum() avg() max() min() count()
SELECT SUM(servlet) AS 'servlet的总成绩' FROM student; SELECT AVG(servlet) AS 'servlet的平均分' FROM student; SELECT MAX(servlet) AS '最高分' FROM student; SELECT MIN(servlet) AS '最低分' FROM student; SELECT COUNT(*) FROM student; // 多少个学生 SELECT COUNT(id) FROM student; //
count() 函数统计的数量不包含null的数据,使用count统计表的记录数,要使用不包含null值的字段
1 SELECT * FROM student LIMIT 0,2; // 查询第1,2条记录(第1页的数据) 2 SELECT * FROM student LIMIT 2,2; // 查询第3,4条记录(第2页的数据) 3 SELECT * FROM student LIMIT 2,2; // 查询第5,6条记录(第3页的数据) 4 SELECT * FROM student LIMIT 6,2; // 查询第7,8条记录 (没有记录不显示)
(10)查询排序(order by)
order by 字段 asc/desc
asc: 顺序,正序。数值:递增,字母:自然顺序(a-z),默认
desc: 倒序,反序。数值:递减,字母:自然反序(z-a)
SELECT * FROM student ORDER BY id ASC;
(11)分组查询(group by)
SELECT gender,COUNT(*) FROM student GROUP BY gender;
(12)分组查询后筛选
SELECT gender,COUNT(*) FROM student WHERE GROUP BY gender HAVING COUNT(*)>2;
四、数据约束
1 CREATE TABLE test( 2 NAME VARCHAR(20), 3 gender VARCHAR(2) DEFAULT '男' 4 ) 5 6 INSERT INTO test(NAME) VALUES('张三'); 7 INSERT INTO test(NAME,gender) VALUES('张三',NULL); 8 9 SELECT * FROM test;
当前没有插入默认值字段的时候,默认值才会起作用
1 CREATE TABLE test( 2 NAME VARCHAR(20) NOT NULL, 3 gender VARCHAR(2) 4 )
不能不插入值。不能为null
3、唯一约束
1 CREATE TABLE test( 2 id INT UNIQUE, 3 NAME VARCHAR(20) 4 )
4、主键约束
注意:
(1)通常情况下,我们会给每张表都会设置一个主键字段,用来标记记录的唯一性
(2)但是不建议把业务含义字段作为主键,因为随着业务的变化,业务字段可能会出现重复。
(3)建议给每张张独立添加一个叫id的字段,把这个id字段设置成主键,用来作为记录的唯一性当有了外键约束之后,操作数据(管理数据)的顺序如下:
插入数据: 先插入主表的数据,再插入副表数据
修改数据: 先修改主表数据,再修改副表数据
删除数据: 先删除副表数据,再删除主表数据
1 // 员工表(副表: 被别的表约束。外键设置在副表) 2 CREATE TABLE employee( 3 id INT PRIMARY KEY AUTO_INCREMENT, 4 NAME VARCHAR(20), 5 deptId INT, 6 CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) 7 // 外键名称 外键字段 参考 8 )
1 // 部门表(主表:约束别人的表) 2 CREATE TABLE dept( 3 id INT PRIMARY KEY AUTO_INCREMENT, 4 NAME VARCHAR(20) 5 )
7、级联技术
级联: 当有了外键的时候,我们希望修改或删除数据的时候,修改或删除了主表的数据,同时能够影响副表的数据,这时就可以使用级联。
1 CREATE TABLE employee( 2 id INT PRIMARY KEY AUTO_INCREMENT, 3 NAME VARCHAR(20), 4 deptId INT, 5 -- 添加级联修改: ON UPDATE CASCADE 6 -- 添加级联删除: ON DELETE CASCADE 7 CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE 8 -- 外键名称 外键字段 参考 9 )
五、数据库设计
六、多表查询
SELECT employee.name,dept.name FROM employee,dept;
多表查询的步骤: 1)确定查询哪些表 2)确定查询哪些字段 3)确定连接条件(规则: 表数量-1)
2、内连接查询(使用最多)
1 SELECT e.name,d.name 2 FROM employee e,dept d 3 WHERE e.deptId=d.id;
1 SELECT e.name,d.name 2 FROM employee e 3 INNER JOIN dept d 4 ON e.deptId=d.id;
3、左外连接查询(其次)
左外连接查询效果: 左表(部门表)的数据全部显示,右表(员工)的数据当满足连接条件的时候,就显示满足条件的的数据,但是如果不满足连接条件,则显示null
1 SELECT d.name,e.name 2 FROM dept d 3 LEFT OUTER JOIN employee e 4 ON d.id=e.deptId;
4、右外连接查询
右外连接查询效果: 右表(部门表)的数据全部显示,左表(员工)的数据当满足连接条件的时候,就显示满足条件的数据,但是如果不满足连接条件,则显示null
1 SELECT d.name,e.name 2 FROM employee e 3 RIGHT OUTER JOIN dept d 4 ON d.id=e.deptId;
5、自连接查询
1 SELECT e.name AS '员工',b.name AS '上司' 2 FROM employee e 3 LEFT OUTER JOIN employee b 4 ON e.bossId=b.id;
七、存储过程
存储过程,其实就是带逻辑的(多个)sql语句。也是sql编程
1、特点:
(1)存储过程保存到数据库服务器端,通过数据库客户端工具调用存储过程
(2)存储过程的效率会非常高!因为存储过程是在数据库服务器端执行。
(3)存储过程的移植性非常差的!
2、语法:
1 创建存储过程 2 -- 定义结束符号 3 DELIMITER 结束符号 4 CREATE PROCEDURE 存储过程名称 (形式参数列表) 5 BEGIN 6 多个sql语句 7 END 结束符号 8 9 -- 调用存储过程 10 CALL 存储过程名称(实际参数列表); 11 12 参数类型: 13 IN: 输入参数,可以携带数据到存储过程中 14 OUT: 输出参数,可以携带数据到存储过程外面。 15 INOUT: 输入输出参数。
1 --带有输入参数的存储过程 2 DELIMITER $ 3 CREATE PROCEDURE pro_testByIn(IN eid INT) -- 参数类型(IN) 参数名称 数据类型(int) 4 BEGIN 5 SELECT * FROM employee WHERE id=eid; 6 END $ 7 8 -- 调用 9 CALL pro_testByIn(2);
1 --带有输出参数的存储过程 2 DELIMITER $ 3 CREATE PROCEDURE pro_testByOut(OUT n VARCHAR(20)) 4 BEGIN 5 -- 修改变量n 6 SET n = '输出参数'; 7 END $
定义变量去接收输出参数数据
--带有输入输出参数的存储过程 DELIMITER $ CREATE PROCEDURE pro_testByInOut(INOUT n VARCHAR(20)) BEGIN -- 查看n变量 SELECT n; -- 修改n变量 SET n = '500'; END $ -- 定义会话变量调用存储过程 SET @n='100'; CALL pro_testByInOut(@n); -- 查看n SELECT @n;
1 --带有判断条件的存储过程 2 DELIMITER $ 3 CREATE PROCEDURE pro_testByIf(IN num INT,OUT str VARCHAR(20)) 4 BEGIN 5 IF num=1 THEN 6 SET str = '星期一'; 7 ELSEIF num= 2 THEN 8 SET str ='星期二'; 9 ELSEIF num=3 THEN 10 SET str = '星期三'; 11 ELSE 12 SET str = '错误参数'; 13 END IF; 14 END $ 15 16 CALL pro_testByIf(5,@str); 17 SELECT @str;
1 --带有循环条件的存储过程 2 DELIMITER $ 3 CREATE PROCEDURE pro_testByWhile(IN num INT,OUT score INT) 4 BEGIN 5 -- int result =0; 6 -- for(int i=1;i<=100;i++){ 7 -- result += i; 8 -- } 9 10 -- 定义局部变量 11 DECLARE i INT DEFAULT 1; 12 DECLARE result INT DEFAULT 0; 13 WHILE i<=num DO 14 SET result = result + i; 15 SET i = i+1; 16 END WHILE; 17 SET score = result; 18 END $ 19 20 21 CALL pro_testByWhile(200,@score); 22 SELECT @score;
1 --携带数据库的数据给输出参数(INTO) 2 DELIMITER $ 3 CREATE PROCEDURE pro_testByData(IN eid INT,OUT sname VARCHAR(20)) 4 BEGIN 5 SELECT NAME INTO sname FROM employee WHERE id=eid; 6 END $ 7 8 CALL pro_testByData(2,@sname); 9 SELECT @sname;ss
3、mysql数据库三种变量:
(1)全局变量。mysql内置的变量,mysql程序关闭的时候全局变量才会失效!!
character_set_client: mysql接收的客户端的数据编码
character_set_results: mysql使用什么编码输出给客户端数据
查看某个全局变量: select @@变量名
修改某个全局变量: set @@变量名=值
(2)会话变量。变量只在某次登录的会话中有效!退出连接,会话变量数据失效!!
查看某个会话变量: select @变量名
修改/定义某个会话变量: set @变量名=值
(3)局部变量:在存储过程中定义的变量。存储过程结束局部变量失效!!
查看某个局部变量: select 变量名
修改某个局部变量: set 变量名=值
定义某个局部变量: declare 变量名 数据类型;
1 -- 定义会话变量去接收输出参数数据 2 -- set @n='eric'; 3 CALL pro_testByOut(@n); 4 -- 查看会话变量n 5 SELECT @n;
八、触发器
当往员工表插入/修改/删除一条数据的时候,同时往日志表记录下来,这时就要使用触发器完成