第十期_SQL 语法_20190707

Python测试开发班 Python测试开发-10期
1

参考连接

SQL语法

DDL

create database seveniruby;
use seveniruby;

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) 
);

CREATE TABLE `Persons` (
  `PersonID` int(11) DEFAULT NULL,
  `LastName` varchar(255) DEFAULT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1



CREATE TABLE `seveniruby`.`temp` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`));

ALTER TABLE `seveniruby`.`temp` 
CHANGE COLUMN `age` `age` INT(11) NULL DEFAULT 18 ;

导入test_db

git clone https://github.com/datacharmer/test_db.git
cd test_db
#修改数据库名字为自己的名字
#修改当前的用户的权限，增加reload与REFERENCES的权限
mysql -h docker.testing-studio.com -u hogwarts -p < employees.sql

DML基本查询

select * from departments;
select dept_name from departments;
select * from departments where dept_no='d002';
select * from departments order by dept_no DESC;
select * from departments order by dept_no DESC limit 3 offset 3;
select gender from employees;
select distinct gender from employees;
select * from employees where gender='F' limit 3;
select * from salaries order by salary DESC limit 100;
select * from salaries order by salary DESC limit 100;
select count(*) from salaries;
select avg(salary) from salaries;
select avg(s.salary) from (select salary from salaries order by salary DESC limit 100) as s;
select count(*), dept_no from dept_manager group by dept_no;

SQL高级语法

  • 给出一个员工的员工号 生日 部门名字
select e.emp_no,birth_date,d.dept_no 
from employees e
inner join dept_emp d
on e.emp_no=d.emp_no
limit 10 

select e.emp_no,birth_date,d.dept_no,n.dept_name 
from employees e 
inner join (dept_emp d, departments n)
on e.emp_no=d.emp_no and n.dept_no=d.dept_no
order by emp_no
limit 1000 

INSERT INTO `seveniruby`.`employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('10000', '2019-07-07', 'sevneiruby', 'hogwarts', 'M', '2019-07-07');

select e.emp_no,birth_date,d.dept_no,n.dept_name 
from employees e 
left join (dept_emp d, departments n)
on e.emp_no=d.emp_no and n.dept_no=d.dept_no
order by emp_no
limit 10

'10000','2019-07-07',NULL,NULL
'10001','1953-09-02','d005','Development'
'10002','1964-06-02','d007','Sales'
'10003','1959-12-03','d004','Production'
'10004','1954-05-01','d004','Production'
'10005','1955-01-21','d003','Human Resources'
'10006','1953-04-20','d005','Development'
'10007','1957-05-23','d008','Research'
'10008','1958-02-19','d005','Development'
'10009','1952-04-19','d006','Quality Management'

INSERT INTO `seveniruby`.`departments` (`dept_no`, `dept_name`) VALUES ('d100', 'Shield');

select * from dept_emp d
right join departments n
on d.dept_no=n.dept_no
where n.dept_no='d100'

NULL,NULL,NULL,NULL,'d100','Shield'


select avg(salary) avg_salary, d.dept_no, dept_name from salaries s
left join (dept_emp d, departments m)
on s.emp_no=d.emp_no and m.dept_no=d.dept_no
group by dept_no
order by avg_salary

MySQL的管理

增加连接

SET GLOBAL max_connections = 500;

性能分析

  • 代码层埋点
  • 查看每个sql的执行时间
  • slow log
  • dtrace systrace
  • excute plan辅助分析瓶颈

MySQL的应用

课间练习

  • 算出各个子部门的平均工资