MYSQL入门

MYSQL

Tables and keys（表格与键，资料储存）

1. column列 row行

2. 主键primary key foreign key外键

   

sql语法

 creat database `sql_tutorial`; 创建数据库``防止与关键字冲突
 show database; 展示数据库
 drop database `database`;删除database
 use `sql_tutorial`;使用
 decimal--有小数点的数
 verchar 字串 
 blob binary large object 圆片影片档案
 date 日期
 timestamp 记录时间
 creat table `student`(
     `student_id` int auto_increment,自动增加
     `student_age` int default 3,
     `name` varchar(20) not null,
     `major` varchar(20) unique,
     `sex` 
     primary key(`student_id`)
 );
 describe `student`;
 ALTER TABLE `student` ADD gpa DECIMAL(3,2);
 ALTER TABLE `student` DROP COLUMN gpa; 删除列
 insert into `student`(`name`,`major`,`student_id`) values(1,'小白','历史');按顺序向第一行填入数据
 select * from `student`;
 ​
 update `student` 
 set `major`=`English` 
 where `major`= `english` or `major`=`history`;
 ​
 delete from `student` where `student_id`=4;
 ​
 delete from `student` where `name`='hui' and `major`='physic'
 <> !=不等于
 where `score`<60;
 ​
 select `name`,`major`from `student` order by `score` desc;
 desc由高到低asc由低到高
 默认是由低到高升序
 order by `score`,`student_id`;当分数相等时以id排序
 limit 3;只返回前三行数据
 select * from `student` where `major`=`English` and `student_id`=1;
 ​
 where `major` in('history','English','biolgy');
 ​

创建公司资料库表格

 创建员工表格
 creat table `employee`(
     `emp_id` int primary key,
     `name` varchar(20),
     `birth_data` date,
     `gender` varchar(1),
     `salary` int,
     `branch_id`int,
     `sup_id` int
 );
 ​
 creat 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`)on delete set null;
 alter table `employee`
 add foreign key(`sup_id`)
 on delete set null;
 创建客户表格
 creat table `client`(
     `client_id` int primary key,
 `client_name`varchar(20),
     `phone` varchar(20)
 );
 在employee和branch两个表格之间创建关联表格works_with
 creat table `work_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
 );
 创建完成，输入数据
 update `branch` set `manager_id`=206 where `branch_id`=1; 
 insert into `client` values(400,'dog','1305');
 .....
 取得用户资料
 select * from `client`;
 select * from `employee` order by `salary` desc;
 select * from `employee` order by `salary` desc limit 3;
 select `name` from `employee`; 
 select distinct `gender` from `employee`;
 聚合函数
 select count(`sup_id`) from `employee`;
 select count(*) from `employee` where `birth_date`>`1970-01-01` and `gender`=`female`;
 select avg(`salary`) from `employee`;
 sum()总和
 max()min()
 select `name` from `employee` where `salary`=(select max(`salary`)from `employee`);
 wildcards 通配符%
 select * from `client` where `phone` like '%335%';like模糊查询
 =运算符只有两个字符完全匹配才会返回true精确查询
 %代表任意个字符，_代表一个字符
 ​
 联合查询union结果合并到一列
 select `name` from `employee` union select `client_name` from `client`;属性数目要一致
 select `emp_id` as `total_id`,`name` as `total_name` from `employee` union select `client_id`,`client_name`from `client`;as重命名
 ​
 join 连接两个表，左右连接
 select * from `employee` join `branch` on `employee`.`emp_id`=`branch`.`manager_id`;
 ​
 from `employee` left join `branch`
 这里`employee`是左表格，不管条件是否成立都会返回左表格数据，但右表格必须成立才返回数据否则返回null
 right join同理
 子查询
 select `name` from `employee` where `emp_id`=(
     select `manager_id`
     from `branch`
     where `branch_name`='研发'
 );
 ​
 select `name` from `employee` where `emp_id` in(
     select `emp_id` from `works_with` where `total_sales`>50000
 );
 ​
 foreign key(`manager_id`) references `employee`(`emp_id`)on delete set null;
 如果`emp_id` 被删除了那么manager_id被设为null
 ​
 foreign key(`emp_id`)references `employee`(`emp_id`) on delete cascade
 如果`emp_id` 被删除了那么其相对应的全部内容删掉
 primary key的值不能被设置为null