Mysql数据库初笔记.docx

《Mysql数据库初笔记.docx》由会员分享，可在线阅读，更多相关《Mysql数据库初笔记.docx（48页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、目录、简单査询语句41、基础査询语句 select4取别名4distinct的使用,去重4査看表格结构42、条件査询5like模糊査询5in： 5not in5between and:介于 xx 之间5is null / isnot null:6作业63、排序査询8练习8作业94、常用函数10#字符串函数10#数学函数12#日期函数13#流程控制函数145、分组函数15二、査询191、连接査询19#等值连接20#非等值连接21#自连接21非等值连接25自连接25外连接26交叉连接:笛卡尔集的效果（cross） 272、子査询27、 where 或 having28二、列子査询（多行）31三、F

2、ROM后面,子査询结果充当从张表,必须取别名33四、exists后面（相关子査询）:判断后面査询字句有没有结果,布尔类型333、分页査询344、联合査询35三、增删改査:数据操作语言DML 36、插入36二、修改语句371 .修改单表记录372 .修改多表记录（补充）37三、删除语句38方法一:DELETE 38方法二：TRUNCATE,删除整个表内容,不可以加where条件40四、数据定义语言DDL41、库的管理411、创建库41更改数据库字符集41库的删除42二、表的管理42表的修改alter43修改列名CHANGE43修改列的类型或约束MODIFY43添加列ADD43删除列drop43修

3、改表名 rename to43表的复制44仅仅复制表的结构44复制表的结果外加数据44只复制部分数据或部分列45仅仅复制某些字段（某部分结构）45五、约束与自增46、约束46添加外键47表级添加主键47列级添加主键47删除主键47删除唯一建48二、自增48、创建表示设置标识列48二、修改表示创建表示列48删除自增49、简单查询语句1V基础查询语句selectselect字段,字段,.from表名;(若是关键字需要加着重号、,)(查询函数)调用函数,获取返回值select database();数据库名select version();select user();数据库用户取别名as： sele

4、ct user AS用户名【别名】:(用户名可加单双引号,特殊符号必须加 双引号)空格:select user () 别名;多个字段查询,别名也可以单独取,放在相应的字段后。多个字段连接并取别名+在mysql中只做加法运算，将连接字段中的值若是有字符串强行转换成数值， 若是无法转换则为0;select字段1+字段二as【空格】别名from表名concat在mysql中用来连接数据库字段,但是只要其中一个字段为空则全部为空。selectconcat(字段 1,字段 2,。)别名 from 表名;distinct的使用,去重select distinct 字段 from 表名:查看表格结构desc

5、表名:show columns from 表名;查看列(columns)ifnull (字段,一)判断字段是否为空,若是空则显示需要输入的值select concat （字段 1,字段 2, ifnull （字段 3,）。）别名 from 表名;2、条件查询条件査询whereNOTlike模糊查询: 任意单个字符,任意多个字符,需用单引号引起来。select 字段 from 表名 where 字段 1 like ;# 查询带有a的姓名SELECT * FROM employees WHERE last_name LIKE %a%;# 第一个字符为a的姓名SELECT * FROM employ

6、ees WHERE last name LIKE a%;# 最后一个字符为。的姓名SELECT * FROM employees WHERE last_name LIKE %a;# 第三个字符为aSELECT * FROM employees WHERE last_name LIKE _a%;# 查询第二个带有下划线的姓名,需要加转义字符、SELECT * FROM employees WHERE last_name LIKE _、；SELECT * FROM employees WHERE last_name LIKE escape $;in：查询某字段的值是否属于指定列表之内not in,

7、与上面的相反。select字段from表名where字段in【值】/ not in()；查询含有30, 50, 90的员信息SELECT last name, department_id FROM employees WHERE department_id IN (30, 50, 90);# 查询种编号不是sh_clerk, it_prog的信息select * from employees where job_id NOT IN(sh_clerk, it_prog);between and!介于 xx 之间# 查询department_id的介于30到90之间SELECT departmen

8、t_id, last_name FROM employees WHERE department_id BETWEEN 30 AND 90;# 查询姓名,资,年薪,且年薪不在10000到20000之间的信息SELECT last_name, salary, commission_pct, salary*12*(1+IFNULL (commission_pct, 0)年 薪FROM employees WHERE salary*12*(l+IFNULL(commission_pct,0) NOT BETWEEN 10000 AND 20000;is null / is not null:判断nul

9、!值,=判断普通内容, 安全等于集合了 is和=的功能# 查询有奖金的人的姓名,奖金SELECT last_name, commission_pct FROM employees WHERE commission_pct is NOT NULL;# 查询没有奖金的人的姓名,奖金SELECT last_name, commission_pct FROM employees WHERE commission_pct is NULL;OR不等于# 不知道优先级可以打小括号SELECT last_name, department id, email FROM employees WHERE NOT (

10、department_id=50 AND department_id 0. 03 OR (employee_id =60 AND employee_id 12000;# 员号为176的姓名和年薪和部门号#SELECT last_name, department_id, salary*12*(1+IFNULL(commission_pct, 0)年薪 FROM employees WHERE employee_id=176;#选择资不在5000到12000的员姓名和资#SELECT last_name, salary FROM employees WHERE salary NOT BETWEEN

11、 5000 AND 12000;# 选择在20或50号部门员的姓名和部门号SELECT last name, department id FROM employees WHERE department !d IN(20, 50);# 选择公司中没有管理者的员姓名及jod_id# SELECT last_name, job_id FROM employees WHERE manager_id IS NULL;# 选择公司中有奖金的员姓名,工资和奖金率# SELECT last name, salary, commission pet FROM employees WHERE commission

12、 pet is not NULL;# 员姓名第三个字母是a的员姓名SELECT last name FROM employees WHERE last name LIKE _a%;# 显示employees表中first_name以e结尾的员信息# SELECT * FROM employees WHERE first_name LIKE %e;# 选择姓名有a和e的员姓名SELECT last_name FROM employees WHERE last_name LIKE;#有问题ttSELECT last_name FROM employees WHERE last_name LIKE

13、%a% AND last_name LIKE %e%;# 显示employees表中部门编号在80至U100之间的姓名,职位ttSELECT last_name, job_id , department_id FROM employees WHERE department_id BETWEEN 80 AND 100;# 显示employees表中managejid是100, 101, 110的员姓名、职位ttSELECT last_name, job_id , manager_id FROM employees WHERE manager_id IN(100, 101, 110)3、排序查询s

14、elect 查询字段from 表where条件】order by排序列表（单个字段,多个字段之间用逗号隔开,表达式,函数,列数,及 以上组合；asc升序默认的，desc降序）执行顺序from where select order by练习# 有奖金的员工按年薪降序排名SELECT *, salary*12*(l+IFNULL(commission pct, 0)年薪 FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary*12*(l+IFNULL(commission_pct, 0) DESC;SELECT *, sala

15、ry*12*(l+IFNULL(commission pet, 0)年薪 FROM employees WHERE commission_pct IS NOT NULL ORDER BY 年薪 DESC;#SELECT *, salary*12*(l+commission_pct)年薪 FROM employees WHERE commission,pct IS NOT NULL ORDER BY 年薪 DESC;#按名字长度排序升序#SELECT LENGTH(last_name) len, last_name FROM employees ORDER BY len ;# 多个字段排序,用逗

16、号隔开。# 查询公司员姓名,资,部门编号,先工资升序,再部门降序SELECT last name, salary, department id FROM employees ORDER BY salary ASC, department_id DESC;# 安列数排序,字母顺序排列的SELECT * FROM employees ORDER BY 2 ; #2是指按照第二列排列,与写字段是 样的效果。#SELECT * FROM employees ORDER BY first_name;#查询员姓名、部门号、年薪,按年薪降序,姓名升序#SELECT last name,department

17、_id, salary*12*(l+IFNULL(commission_pct, 0) 年薪 FROM employees ORDER BY 年薪 desc, last_name;#选择资不在8000到17000的员姓名和资,按资降序SELECT last name, salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;#查询邮箱中包含e的员信息,先按邮箱的字节数降序,再按部门号升序#SELECT * FROM employees WHERE email LIKE %e% ORDE

18、R BY LENGTH(email) DESC, department_id;4、常用函数#字符串函数#CONCAT （字符串,字段,.）,拼接字符函数,可以写入多个字符串或是字段#SELECT CONCAT（last_name, * hellow*） from employees;#LENGTH（str）,可写入字符串或是字段,获取字节长度#SELECT LENGTH（哈哈哈哈,嘻嘻嘻）；#CHAR_LENGTH（str）可写入字符串或是字段,获取字符长度,有几个字符SELECT CHAR LENGTH C 哈哈哈哈，嘻嘻嘻）；#SUBSTRING（字符串,起始索引，截取字符长度）截取字串,

19、起始索引是1开始:#SELECT SUBSTRING（,张三丰爱上了郭襄，3, 3）;#SELECT SUBSTRING （,张三丰爱上了郭襄，3, I）;#SUBSTRING（字符串,起始索引）,截取从起始索引及其之后的字符#SELECT SUBSTRING（,张三丰爱上了郭襄，3）;重载#SUBSTR（str FROM pos FOR len）,与截取字符串样SELECT SUBSTR（,张三丰爱上了郭襄，2, 4）;#LEFTRIGHT（字符串,截取长度）,从左右开始截取#SELECT LEFT （ asdfg, 2）;SELECT RIGHT( asdfg,2);#INSTR(字符串,

20、需要查询的第一次出现的字符串),获取字节第一次出现的索引SELECT INSTR (三打白骨金aaa白骨金白骨金白骨金，白骨金)；#TRIM(指定需要去除的字符from字符串)，默认的是去除前后空格,指定去除字符 串前后的字符#SELECT TRIMC 哈哈哈 嘻嘻嘻)；#SELECT TRIMC x FROM xxxxxx 哈哈哈 xx 嘻嘻嘻 xxxxx);#LPADRPAD(字符串，填充长度,填充字符),左右填充#SELECT LPADC 虚竹，10,西夏公主)；SELECT RPADC 虚竹，10,西夏公主)；#UPPER(str)变大写字符,LOWER (str)变小写字符#查询员表

21、的姓名,姓首字母大写,其他小写;名的所以字符小写,姓和名之间 用一分割,取别名U0TPUTSELECTCONCAT(UPPER(SUBSTRING(last_name, 1, 1), LOWER(SUBSTRING(last_name, 2), LOWER( first_name) OUTPUTFROM employees;#STRCMP(字符串,字符串)比较两个字符串的大小，第一个先比较若是同再比较第 二个，若是不同则结束比较SSELECT STRCMPC 郭襄，张三丰); 1#SELECT STRCMPC 张三丰，郭襄)；10000,显示级别C否则显示DSELECT salary,CASE

22、WHEN salary20000 THEN AWHEN salary15000 THEN，BWHEN salary10000 THEN CELSE，DEND级别FROM employees;5、分组函数# 分组函数# 单行函数一对的感觉,分组函数,将多个值计算汇总返回为个值# 分组函数叫聚合函数或是统计函数,用于将一组数据进行统计计算。# sum （字段名）:求和# avg （字段名）:求平均值#max （字段名）：求最大值#min (字段名):求最小值# count(字段名):计算非空字段的个数,自动去掉空值。统计结果集的函数推荐用count.(*)并查询员的工资和,资平均值,最低工资,最高

23、工资,有工资个数SELECT SUM (salary)总资,AVG (salary)平均资,MIN (salary)最低 资,MAX(salary)最高工资,COUNT (salary)有资 from employees;# 添加帅选条件# 查询有佣金的记录数SSELECT COUNT(salary) FROM employees;# 查询月薪大于2500的人数ttSELECT COUNT(salary) from employees WHERE salary 2500;并查询有领导的人数SELECT COUNT(manager id) FROM employees;肥ount (*), co

24、unt (I)=count (*)只要表内其中一行中一个有值,则算入统计。计算表内 的函数，也可以加条件# SELECT COUNT(*) FROM employees;SELECT COUNT(*) FROM employees WHERE department id=30;# 搭配dstinct实现去重统计# 查询有几个部门有员SELECT COUNT(DISTINCT department-id) from employees;# 每个部门的总资、平均资# 分组查询,需要先分组,然后在使用分组函数SELECT department_id,SUM(salary),AVG(salary) F

25、ROM employees GROUP BY department_id;# 查询每个领导手下的人数;一般xx数用count,不确定用什么字段就# SELECT manager id, COUNT(*) FROM employees GROUP BY manager id;# 查询邮箱中包含a字符的,每个部门的最高高能工资# SELECT MAX(salary),email FROM employees WHERE email LIKE %a% GROUP BY department id;# 查询每个领导手下有奖金的员的平均资SELECT AVG (salary* (l+commission

26、_pct)平均资，manager_id FROM employees WHERE commission_pct is NOT NULL GROUP BY manager_id;# 查询哪个员工部门的人数大于5SELECT COUNT(*)员个数,department一id FROM employees GROUP BY department_id HAVING COUNT(*)5;# 每个种有奖金的员的最高工资12000的工种编号和最髙工资# SELECT job_id, MAX(salary) FROM employees WHERE commission_pct is not null G

27、ROUP BY job_id HAVING MAX(salary)12000;# 领导编号大于102的每个领导手下最低工资大于5000的最低工资# SELECT manager id, MIN(salary) FROM employees WHERE manager_id102 GROUP BY managerid HAVING MIN(salary)5000;#查询没有奖金员的最高工资6000的工种编号和最广告资,按最高工资升序SELECT job_id, MAX (salary) FROM employees WHERE commission_pct is null GROUP BY jo

28、b_id HAVING MAX(salary)6000 ORDER BY MAX(salary);# 查询每个种每个部门的最低工资,并按最低工资降序SELECT MIN(salary),job_id, department_id FROM employees GROUP BY job id, department id ORDER BY MIN(salary) desc;# 查询各个管理者手下员的最低工资,其中最低工资不能低于6000,没有管理者不计 入统计SELECT MIN (salary), manager_id FROM employees WHERE manager_id is no

29、t null GROUPBY manager_id HAVING MIN(salary)=6000;# 查询所有部门编号,员工数量和工资平均值,并按平均资降序SELECT department id, COUNT(employee_id), AVG(salary) from employees GROUP BY department_id ORDER BY AVG(salary) desc;# 选择具有各个job_id的员工数SELECT COUNT(*), job_id FROM employees WHERE job_id is not null GROUP BY job_id二、查询1、

30、连接查询# 连接查询，多表查询# 笛卡尔集错误表1有m行、表2有n行,结果是m*n,就是未加任何有效的连接条 件造成:# 添加有效的连接条件可避免# SELECT beauty, name , boyName FROM boys, beauty WHERE beauty. boyfriend_id= boys, id;#sqll92 标准#等值连接, 显示两个表的交集部分,若是表取了别名,则后面条件只可以用别 名。- 查询员姓名、种号、种名一 SELECT last name, employees, job id,job title- - from employees, jobs- - WHE

31、RE employees, job id=jobs. job id;- 2,查询有奖金的员名,部门名- SELECT last_name, department_name FROM employees e, departments d一 WHERE commission pct is not null AND e. department_id=d. department !d;- 3.查询城市中第二个字母为的部门和城市名(加筛选)- - SELECT department name, city- FROM departments d,locations 1一 WHERE city LIKE 0

32、% AND 1. location_id=d. location id;- 4.查询每个城市的部门个数(加分组)- - SELECT COUNT(*)个数,city, department name- - FROM locations 1, departments d- - WHERE 1. location_id=d. location_id- - GROUP BY city;- 5,查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资一 SELECT department name, d. manager_id, MIN(salary)- FROM departments d,

33、employees e一 WHERE commission_pct is not null一 and d. department_id=e. department_id- GROUP BY department name;#查询每个工种的工种名和员个数,并按员个数降序(排序)SELECT job_title, COUNT(*)一 FROM employees e,jobs j一 WHERE e. job_id=j. job_id GROUP BY job_title ORDER BY COUNT(*) desc;# 6.实现三表连接# 查询员名部门名和所在城市# SELECT last nam

34、e, department. name, city# from employees e, departments d, locations 1一 WHERE e. department_id=d. department id and d. location id=l. location id;#非等值连接# 1.查询员资和资级别# - SELECT salary, grade_level FROM employees, job grades# - WHERE salary BETWEEN lowest sal AND highest sal;# 自连接 同个表看作是两个不同的表进行连接,取不同

35、的别名进行查询#P77作业# sqll99标准【推荐】# 内连接,外连接左,右,全外,交叉连接/*SELECT查彻列表FROM表1连接类型（内:inner、外左left,外右right,全外full、交叉连接:cross）JOIN表2 （相当于,）ON连接条件（之前连接条件和筛选条件都是where现在区分开来）WHERE筛选条件# /*内连接inner可以省略等值连接实现效果和sql92语法是样的SELECT查询列表FROM 表 1INNER JOIN 表 2ON连接条件【等值连接,非等值连接,自连接】#1.查询员名、部门名/SELECT last name, department_nameF

36、ROM employees e INNER JOIN departments dON e. department id=d. department id;*/*SELECT last_name, department_nameFROM employees e , departments dWHERE e. department_id=d. department_id;*/*查询名字中包含e的员名和工种名SELECT last_name, job_titleFROM employees e INNER JOIN jobs jON e. job_id=j. job_idWHERE last_nam

37、e LIKE %e%;查询部门个数大于3的城市名和部门个数SELECT city, count (*)部门数FROM locations 1 INNER JOIN departments dON 1. location_id=d. location idGROUP BY cityHAVING count(*)3;*/*查询那个部门员工个数大于3的部门名和员个数,并按个数降序SELECT department_name, COUNT (*)员工个数FROM departments d INNER JOIN employees eON d. department_id=e, department_

38、idGROUP BY department nameHAVING COUNT(*)3ORDER BY COUNT(*) desc;*/*查询员姓名,工种名,并按部门名降序SELECT last_name, job_titleFROM jobs jINNER JOIN employees eON j. job_id=e. job idINNER JOIN departments dON e. departrnent_id=d. department idORDER BY department name desc;/*非等值连接查询员资级别SELECT grade_level, salaryFRO

39、M job grades g JOIN employees eON salary BETWEEN lowest_sal AND highest_sal;查询资级别大于20的个数,并按资级别降序SELECT COUNT (*)资级别个数,grade_levelFROM job_grades g JOIN employees eON salary BETWEEN lowest_sal AND highest_salGROUP BY grade levelHAVING COUNT(*)20ORDER BY gradelevel desc;*/*自连接查询员的姓名、上级姓名SELECT e. last

40、_name 员姓名,m. last_name 上级姓名FROM employees e JOIN employees mON e. manager_id=m. employee_id;查询员含有k字符的姓名、上级姓名SELECT e. last_name 员姓名,m. last_name 上级姓名FROM employees e JOIN employees mON e. manager_id=m. employee_idWHERE e. last_name LIKE %k%;外连接用于查询个表中有另一个表中没有的数据,分主表和从表外连接的查询结果为主表中的左右记录若主表中有匹配的信息则显示匹配值,若无则显示null外连接查询结果=内连接结果+主表中有而从表中没有的记录左外连接:left join左边是主表右外连接:right jion右边是主表全外连接:内连接结果+表1中有表2中没有的+表2中有表1中没有的（mysql不支持）左外右外两表交换顺序,可以实现同样效果查询男朋友不在男生表中的女生名SELECT name ,bo.*FROM beauty b LEFT JOIN boys boON b. boyfriend_id=bo. idWHERE bo. id