目录
二:将表 employees 的 mobile 字段修改到 officeCode 字段后面
三:将表 employees 的 birth 字段改名为 employee _ birth 。
四:修改 sex 字段,数据类型为 CHAR (1),非空约束。
六:增加字段名 favoriate_activity ,数据类型为 VARCHAR (100)。
八:将表 employees 名称修改为 employees _ Info
一:查看该库下几个表以及查看两张表结构。
SHOW DATABASES;
# 查看表结构的两种方法
DESC 表名
DESCRIBE 表名
二:将表 employees 的 mobile 字段修改到 officeCode 字段后面
ALTER TABLE dept MODIFY mobile CHAR(10) AFTER officeCode;
三:将表 employees 的 birth 字段改名为 employee _ birth 。
ALTER TABLE dept CHANGE birth employee_birth VARCHAR(100);
四:修改 sex 字段,数据类型为 CHAR (1),非空约束。
ALTER TABLE dept MODIFY sex CHAR(1) NOT NULL;
五:删除字段 note
ALTER TABLE dept DROP note;
六:增加字段名 favoriate_activity ,数据类型为 VARCHAR (100)。
ALTER TABLE dept ADD favoriate_activity VARCHAR(100);
七:删除表offices .
DROP TABLE offices
八:将表 employees 名称修改为 employees _ Info
ALTER TABLE employees RENAME employees_info;
重复记录:
缺省情况下查询显示所有行,包括重复行
SELECT department_id
FROM employees;
使用DISTINCT关键字可从查询结果中清除重复行
SELECT DISTINCT department_id
FROM employees;
DISTINCT的作用范围是后面所有字段的组合
SELECT DISTINCT department_id , job_id
FROM employees;
限制所选择的记录:
使用WHERE子句限定返回的记录
WHERE子句在FROM 子句后
SELECT[DISTINCT] {*, column [alias], ...}
FROM table–[WHEREcondition(s)];
WHERE中的字符串和日期值
字符串和日期要用单引号扩起来
字符串是大小写敏感的,日期值是格式敏感的
SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = "king";
WHERE中比较运算符:
SELECT last_name, salary, commission_pct
FROM employees
WHERE salary<=1500;
其他比较运算符
使用BETWEEN运算符显示某一值域范围的记录
SELECTlast_name, salary
FROM employees
WHERE salary BETWEEN 1000 AND 1500;
使用IN运算符
使用IN运算符获得匹配列表值的记录
SELECTemployee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (7902, 7566, 7788);
使用LIKE运算符
使用LIKE运算符执行模糊查询
查询条件可包含文字字符或数字
(%) 可表示零或多个字符
( _ ) 可表示一个字符
SELECT last_name
FROM employees
WHERE last_name LIKE '_A%';
使用IS NULL运算符
查询包含空值的记录
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
逻辑运算符
使用AND运算符
AND需要所有条件都是满足T.
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary>=1100–4 AND job_id='CLERK';
使用OR运算符
OR只要两个条件满足一个就可以
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary>=1100 OR job_id='CLERK';
使用NOT运算符
NOT是取反的意思
SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('CLERK','MANAGER','ANALYST');
使用正则表达式:REGEXP
<列名> regexp '正则表达式'
select * from product where product_name regexp '^2018';
数据分组--GROUP BY
GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。
分组的含义是:把该列具有相同值的多条记录当成一组记录处理,最后只输出一条记录。
分组函数忽略空值,。
结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句。
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
#每个部门的平均工资
SELECT deptno,AVG(sal) FROM TB_EMP GROUP BY deptno
#查每个部门的整体工资情况
#如果select语句中的列未使用组函数,那么它必须出现在GROUP BY子句中
#而出现在GROUP BY子句中的列,不一定要出现在select语句中
SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
FROM TB_EMP
GROUP BY deptno #根据部门编号分组
#每个部门每个职位的平均工资
SELECT deptno,job,AVG(sal) FROM TB_EMP GROUP BY deptno,job
分组函数重要规则
如果使用了分组函数,或者使用GROUP BY 的查询:出现在SELECT列表中的字段,要么出现在组合函数里,
要么出现在GROUP BY 子句中。
GROUP BY 子句的字段可以不出现在SELECT列表当中。
使用集合函数可以不使用GROUP BY子句,此时所有的查询结果作为一组。
数据分组--限定组的结果:HAVING子句
HAVING子句用来对分组后的结果再进行条件过滤。
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BYcolumn];
HAVING子句用来对分组后的结果再进行条件过滤。
#查询部门平均工资大于2000的
#分组后加条件 使用having
#where和having都是用来做条件限定的,但是having只能用在group by之后
SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
FROM TB_EMP
GROUP BY deptno
HAVING AVG(sal) > 2000
HAVING与WHERE的区别
WHERE是在分组前进行条件过滤, HAVING子句是在分组后进行条件过滤,WHERE子句中不能使用聚合函数,
HAVING子句可以使用聚合函数。
组函数的错误用法
不能在WHERE 子句中限制组.
限制组必须使用HAVING 子句.
不能在WHERE 子句中使用组函数
补充:MySQL 多行数据合并 GROUP_CONCAT
Syntax: GROUP_CONCAT(expr)
示例:fruits表按s_id,将供应水果名称合并为一行数据
mysql> select s_id,group_concat(f_name)
-> from fruits
-> group by s_id;
+------+-------------------------+
| s_id | group_concat(f_name) |
+------+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | orange,banana,grape |
| 103 | apricot,coconut |
| 104 | berry,lemon |
| 105 | melon,xbabay,xxtt |
| 106 | mango |
| 107 | xxxx,xbababa |
+------+-------------------------+
7 rows in set (0.00 sec)
注意:使用 GROUP_CONCAT()函数必须对源数据进行分组,否则所有数据会被合并成一行
对结果集排序
查询语句执行的查询结果,数据是按插入顺序排列
实际上需要按某列的值大小排序排列
按某列排序采用order by 列名[desc],列名…
设定排序列的时候可采用列名、列序号和列别名
如果按多列排序,每列的asc,desc必须单独设定
联合查询
-- 中国或美国城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION 去重复
UNION ALL 不去重复查询结果限定
在SELECT语句最后可以用LIMLT来限定查询结果返回的起始记录和总数量。MySQL特有。
SELECT … LIMIT offset_start,row_count;
offset_start:第一个返回记录行的偏移量。默认为0.
row_count:要返回记录行的最大数目。
例子:
SELECT * FROM TB_EMP LIMIT 5;/*检索前5个记录*/
SELECT * FROM TB_EMP LIMIT 5,10;/*检索记录行6-15*/
MySQL中的通配符:
MySQL中的常用统配符有三个:
%:用来表示任意多个字符,包含0个字符
_ : 用来表示任意单个字符
escape:用来转义特定字符
示例: SELECT * FROM dept2 WHERE dep_desc LIKE '/_1111' ESCAPE '/';
触发器
## 示例1:
1. 创建表
mysql>
create table student(
id int unsigned auto_increment primary key not null,
name varchar(50)
);
mysql>
insert into student(name) values('jack');
create table student_total(total int);
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;
# 其他SQL
END$$
mysql> delimiter ;
查看触发器
1. 通过SHOW TRIGGERS语句查看
SHOW TRIGGERS
2. 通过系统表triggers查看
USE information_schema;
SELECT * FROM triggers;
SELECT * FROM triggers WHERE TRIGGER_NAME='触发器名称';
删除触发器
通过DROP TRIGGERS语句删除
sql> DROP TRIGGER 解发器名称
示例2:
创建表tab1
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1(
id int primary key auto_increment,
name varchar(50),
sex enum('m','f'),
age int
);
创建表tab2
DROP TABLE IF EXISTS tab2;
CREATE TABLE tab2(
id int primary key auto_increment,
name varchar(50),
salary double(10,2)
);
触发器tab1_after_delete_trigger
作用:tab1表删除记录后,自动将tab2表中对应记录删除
DELIMITER $$
CREATE TRIGGER tab1_after_delete_trigger
AFTER DELETE ON tab1
FOR EACH ROW
BEGIN
DELETE FROM tab2 WHERE NAME=old.name;
END$$
DELIMITER ;
触发器tab1_after_update_trigger
作用:当tab1更新后,自动更新tab2
DELIMITER $$
CREATE TRIGGER tab1_after_update_trigger
AFTER UPDATE ON tab1
FOR EACH ROW
BEGIN
UPDATE tab2 SET NAME=new.name WHERE NAME=old.name;
END$$
DELIMITER ;
触发器tab1_after_insert_trigger
作用:当tab1增加记录后,自动增加到tab2
DELIMITER $$
CREATE TRIGGER tab1_after_insert_trigger
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
INSERT INTO tab2 VALUES(NULL, new.name, 5000);
END$$
DELIMITER ;
存储过程创建与调用
创建存储过程语法 :
create procedure sp_name(参数列表)
[特性...]过程体
存储过程的参数形式:[IN | OUT | INOUT]参数名 类型
IN 输入参数
OUT 输出参数
INOUT 输入输出参数
delimiter $$
create procedure 过程名(参数列表)
begin
SQL语句
end $$
delimiter ;
调用:
call 存储过程名(实参列表)
存储过程三种参数类型:IN, OUT, INOUT:
===================无参数情况========================
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
SELECT COUNT(*) FROM mysql.user;
END$$
DELIMITER ;
CALL p1();
create table t1(
id int,
name varchar(50)
);
DELIMITER $$
CREATE PROCEDURE autoinsert1()
BEGIN
DECLARE i INT DEFAULT 1; # int i = 1;
WHILE(i<20000)DO
INSERT INTO t1 VALUES(i, MD5(i));
SET i=i+1;
END WHILE;
END$$
DELIMITER ;
CALL autoinsert1();
====================输入参数==========================
DELIMITER $$
CREATE PROCEDURE autoinsert2(IN a INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE(i<=a)DO
INSERT INTO t1 VALUES(i,MD5(i));
SET i=i+1;
END WHILE;
END$$
DELIMITER ;
CALL autoinsert2(20);
-- 定义变量实现
SET @num=20;
SELECT @num;
-- 调用
CALL autoinsert2(@num);
====================OUT=======================
DELIMITER $$
CREATE PROCEDURE p2 (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t1;
END$$
DELIMITER ;
select @a;
+------+
| @a |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
CALL p2(@a);
Query OK, 0 rows affected (0.00 sec)
SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
===================IN 和 OUT=====================
作用:统计指定部门的员工数
DELIMITER $$
CREATE PROCEDURE count_num(IN p1 VARCHAR(50), OUT p2 INT)
BEGIN
SELECT COUNT(*) INTO p2 FROM student WHERE dept=p1;
END$$
DELIMITER ;
call count_num('hr',@a);
select @a;
作用:统计指定部门工资超过例如5000的总人数
DELIMITER $$
CREATE PROCEDURE count_num(IN p1 VARCHAR(50), IN p2 FLOAT(10,2), OUT p3 INT)
BEGIN
SELECT COUNT(*) INTO p3 FROM student WHERE dept=p1 AND salary>=p2;
END$$
DELIMITER ;
CALL count_num('hr',5000,@a);
SELECT @a;
====================INOUT======================
CREATE PROCEDURE proce_param_inout(INOUT p1 INT)
BEGIN
IF (p1 IS NOT NULL) THEN
SET p1=p1+1;
ELSE
SELECT 100 INTO p1;
END IF;
END$$
DELIMITER ;
mysql> select @h;
+------+
| @h |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
call proce_param_inout(@h);
Query OK, 1 row affected (0.00 sec)
mysql> select @h;
+------+
| @h |
+------+
| 100 |
+------+
1 row in set (0.00 sec)
mysql> call proce_param_inout(@h);
Query OK, 0 rows affected (0.00 sec)
mysql> select @h;
+------+
| @h |
+------+
| 101 |
+------+
1 row in set (0.00 sec)