mysql基础知识1

1. 基础语法

create database `database`;
show databases;
drop database `database`;

#指定使用某数据库 
use `sql_tutorial`;

#禁用自带的更新(禁用安全更新 )
set sql_safe_updates = 0;

#创建表格 
create table `student`(
	`student_id` int primary key auto_increment, -- 声明主键、增序生成 
    `name` varchar(20) not null, -- 限制元素不能为null
    `major` varchar(20) default '历史', -- 默认为历史 
    `score` int
    -- primary key(`student_id`)
);

#展示、删除表
describe `student`;
drop table `student`;

#添加、删除列
alter table `student` add gpa decimal(3,2);
alter table `student` drop column gpa;
-- 修改列名 
alter table `student`
rename column name to names;

#写入资料 
insert into `student` values(1, '小白', '历史', 50);
insert into `student`(`name`, `major`, `score`) values('bob', '数学', 67);
select * from `student`;

#替换资料 
update `student`
set `major` = '生数'
where `major` = '生物' or `major` = '数学';

update `student`
set `name` = 'jack', `major` = '物理'
where `student_id` = 2;

#删除资料
delete from `student`
where `student_id` = 4;

delete from `student`
where `name` = 'jack' and `major` = '物理';

delete from `student`
where `score` < 70; -- <>表示不等于,!=

#清空表格
delete from `student`;

#如何搜寻资料
select * from `student`;

select `name`, `major` from `student`; -- 某些列

-- 按顺序排列取得 
select * from `student` order by `score`; -- 默认asc，顺序
select * from `student` order by `score` desc; -- 逆序
select * from `student` order by `score`, `student_id`; 
-- 先按score排序，如果一样就按student_id排序

-- 限制回传数量
select * 
from `student` 
order by `score` desc
limit 3;

-- 条件判断
select * 
from `student` 
where `major` = '英语';

select *
from `student`
where `major` in('历史', '英语', '生物'); -- 等价于or


#创建公司资料库
-- 创建员工表格 
create table `employee`(
	`emp_id` int primary key unique,
    `name` varchar(20),
    `birth_date` date,
    `gender` varchar(1),
    `salary` int,
    `branch_id` int,
    `sup_id` int
);

-- 创建部门表格 
create table `branch`(
	`branch_id` int primary key,
    `branch_name` varchar(20),
    `manager_id` int,
    foreign key (`manager_id`) references `employee`(`emp_id`) on delete set null
);

alter table `employee`
add foreign key(`branch_id`)
references `branch`(`branch_id`)
on delete set null;

alter table `employee`
add foreign key(`sup_id`)
references `employee`(`emp_id`)
on delete set null;

describe `employee`;

-- 创建客户表格 
create table `client`(
	`client_id` int primary key,
    `client_name` varchar(20),
    `phone` varchar(20)
);

-- worwith表格
create table `works_with`(
	`emp_id` int,
    `client_id` int,
    `total_sales` int,
    primary key(`emp_id`, `client_id`),
    foreign key (`emp_id`) references `employee`(`emp_id`) on delete cascade,
    foreign key (`client_id`) references `client`(`client_id`) on delete cascade
);

-- 新增公司资料 
-- 新增部门资料 
insert into `branch` values(1, 'yanfa', null);
insert into `branch` values(2, 'xingzheng', null);
insert into `branch` values(3, 'zixun', null);

-- 新增员工资料
insert into `employee` values(206, 'yellow', '1998-10-08', 'F', 50000, 1, null);
insert into `employee` values(207, 'green', '1985-9-16', 'M', 29000, 2, 206);
insert into `employee` values(208, 'black', '2000-12-19', 'M', 35000, 3, 206);
insert into `employee` values(209, 'white', '1997-01-22', 'F', 39000, 3, 207);
insert into `employee` values(210, 'blue', '1925-11-10', 'F', 84000, 1, 207);

update `branch`
set `manager_id` = 208
where `branch_id` = 3;
select * from `branch`;

-- 新增客户资料
insert into `client` values(400, 'dog', '2165465');
insert into `client` values(401, 'cat', '2198565');
insert into `client` values(402, 'jack', '21687985');
insert into `client` values(403, 'pussy', '123565');
insert into `client` values(404, 'erik', '21656546');

-- 新增works_with
insert into `works_with` values(206, '400', '70000');
insert into `works_with` values(207, '401', '24000');
insert into `works_with` values(208, '402', '9800');
insert into `works_with` values(208, '403', '24000');
insert into `works_with` values(210, '404', '87940');


#取得所有员工资料 
select * from `employee`;

#取得所有客户资料 
select * from `client`;

#按薪水低到高取得员工资料 
select *
from `employee`
order by `salary`;

#取得薪水前三高的员工 
select *
from `employee`
order by `salary` desc
limit 3;

#取得所有员工的名字
select `name` from `employee`;

select distinct `sex` from `employee`;



#聚合函数 
-- 取得员工人数 
select count(*) from `employee`;
select count(`sup_id`) from `employee`;

-- 取得所有出生于1970-01-01之后的女性员工人数 
select count(*) 
from `employee` 
where `birth_date` > '1970-01-01' and `sex` = 'F';

-- 取得所有员工的平均薪水 
select avg(`salary`) from `employee`;

-- 薪水总和 
select sum(`salary`) from `employee`;

-- 薪水最高 
select max(`salary`) from `employee`;

-- 薪水最低 
select min(`salary`) from `employee`;

#通配符 wildcards %代表多个字符串 _代表一个 
select * 
from `client` 
where `phone` like '%565';-- '234%', -- '%345%'

select * 
from `client` 
where `client_name` like 'p%';

select *
from `employee` 
where `birth_date` like '_____12%';


#union 并集 数据类型要相同 
-- 员工客户名字合并 
select `name`
from `employee`
union
select `client_name`
from `client`
union
select `branch_name`
from `branch`;

-- 员工id+员工名字 union 客户id+客户名字
select `emp_id` as `total_id`, `name` as `total_name`
from `employee`
union
select `client_id`, `client_name`
from `client`;

-- 合并薪水和销售额 
select `salary` as `total_money`
from `employee`
union
select `total_sales`
from `works_with`;


#join连接 

-- 取得所有部门经经理的名字 
select `emp_id`, `name`, `branch_name`
from `employee` left join `branch` -- 不论left表格条件成立与否都返回，而右边的要条件成立才返回。
on `employee`.`emp_id` = `branch`.`manager_id`;-- where也可以


#subquery 子查询 
-- 找出研发部门的经理名字 
select `name`
from `employee`
where `emp_id` = (
	select `manager_id`
	from `branch`
	where `branch_name` = 'yanfa'
);

-- 找出以为客户销售金额超过50000的员工名字 
select `name`
from `employee` 
where `emp_id` in (
	select `emp_id`
	from `works_with`
	where `total_sales` > 50000
);


# on delete
create table `branch`(
	`branch_id` int primary key,
    `branch_name` varchar(20),
    `manager_id` int,
    foreign key (`manager_id`) references `employee`(`emp_id`) on delete set null 
    -- 如果对应的emp_id不存在，就设置为null。主键不能设置为null
);

create table `works_with`(
	`emp_id` int,
    `client_id` int,
    `total_sales` int,
    primary key(`emp_id`, `client_id`),
    foreign key (`emp_id`) references `employee`(`emp_id`) on delete cascade,
    foreign key (`client_id`) references `client`(`client_id`) on delete cascade 
    -- 如果对应的emp_id不存在，则跟着一起删除
);

delete from `employee`
where `emp_id` = 207;

select * from `branch`;
select * from `works_with`

2. 结合python使用

import mysql.connector

connection = mysql.connector.connect(host = 'localhost',
                                     port = '3306',
                                     user = 'root',
                                     password = '1234',
                                     database = 'sql_tutorial'#可以事先指定使用的数据库
)

cursor = connection.cursor()

#cursor.execute("create database `qq`;")

# cursor.execute("show databases;")
# records = cursor.fetchall()
# for r in records:
#     print(r)

#cursor.execute("use `sql_tutorial`;")
#cursor.execute("create table `qq`(qq int);")

cursor.execute("SELECT * FROM `branch`;")
records = cursor.fetchall()
for r in records:
    print(r)


cursor.close()
#connection.commit() 需要修改资料的情况，结尾要多加一条语句
connection.close()