*DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
DQL---数据查询语言(select)*
创建表
mysql> create table emp(emp_id int,emp_name char(yint, dpt_id int);
mysql> insert into emp values(1,'robin',30,200);
···················
mysql> insert into emp values(10,'xinghh',43,300)
mysql> select * from emp;
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 1 | robin | 30 | 200 |
| 2 | zorro | 25 | 200 |
| 3 | tom | 22 | 200 |
| 4 | jerry | 31 | 100 |
| 5 | jack | 32 | 100 |
| 6 | rose | 33 | 100 |
| 7 | king | 40 | 300 |
| 8 | jean | 41 | 300 |
| 9 | natasha | 42 | 300 |
| 10 | xinghh | 43 | 300 |
+--------+----------+---------+--------+
排序检索select
mysql> select emp_name,emp_age from emp; # select xx from x;
+----------+---------+
| emp_name | emp_age |
+----------+---------+
| robin | 30 |
| zorro | 25 |
| tom | 22 |
| jerry | 31 |
| jack | 32 |
| rose | 33 |
| king | 40 |
| jean | 41 |
| natasha | 42 |
| xinghh | 43 |
+----------+---------+
mysql> select emp_age,emp_name emp_age from emp; # 检索的过程中重命名
+---------+---------+
| emp_age | emp_age |
+---------+---------+
| 30 | robin |
| 25 | zorro |
| 22 | tom |
| 31 | jerry |
| 32 | jack |
| 33 | rose |
| 40 | king |
| 41 | jean |
| 42 | natasha |
| 43 | xinghh |
+---------+---------+
mysql> select emp_age,emp_name,emp_age as new_age # 在拥有的原有列添加新的一列并重命名
+---------+----------+---------+
| emp_age | emp_name | new_age |
+---------+----------+---------+
| 30 | robin | 30 |
| 25 | zorro | 25 |
| 22 | tom | 22 |
| 31 | jerry | 31 |
| 32 | jack | 32 |
| 33 | rose | 33 |
| 40 | king | 40 |
| 41 | jean | 41 |
| 42 | natasha | 42 |
| 43 | xinghh | 43 |
+---------+----------+---------+
distinct 去重
mysql> select dpt_id from emp;
+--------+
| dpt_id |
+--------+
| 200 |
| 200 |
| 200 |
| 100 |
| 100 |
| 100 |
| 300 |
| 300 |
| 300 |
| 300 |
+--------+
mysql> select distinct dpt_id from emp; # 去重
+--------+
| dpt_id |
+--------+
| 200 |
| 100 |
| 300 |
+--------+
order by 排序
mysql> select emp_name,emp_age from emp order by emp_age; # 按照 emp_age排序
+----------+---------+
| emp_name | emp_age |
+----------+---------+
| tom | 22 |
| zorro | 25 |
| robin | 30 |
| jerry | 31 |
| jack | 32 |
| rose | 33 |
| king | 40 |
| jean | 41 |
| natasha | 42 |
| xinghh | 43 |
+----------+---------+
mysql> select emp_name,emp_age from emp order by emp_age desc; # desc按照emp_age 反向排序
+----------+---------+
| emp_name | emp_age |
+----------+---------+
| xinghh | 43 |
| natasha | 42 |
| jean | 41 |
| king | 40 |
| rose | 33 |
| jack | 32 |
| jerry | 31 |
| robin | 30 |
| zorro | 25 |
| tom | 22 |
+----------+---------+
mysql> insert into emp values(11,'xingxx',43,300);
mysql> select emp_name,emp_age from emp order by emp_age,emp_name; # 先按emp_age排序emp_age相同再按name排序
+----------+---------+
| emp_name | emp_age |
+----------+---------+
| tom | 22 |
| zorro | 25 |
| robin | 30 |
| jerry | 31 |
| jack | 32 |
| rose | 33 |
| king | 40 |
| jean | 41 |
| natasha | 42 |
| xinghh | 43 |
| xingxx | 43 |
+----------+---------+
mysql> select emp_name,emp_age from emp order by emp_age desc,emp_name desc; 先按emp_age反向排序，再按emp_name反向排序
+----------+---------+
| emp_name | emp_age |
+----------+---------+
| xingxx | 43 |
| xinghh | 43 |
| natasha | 42 |
| jean | 41 |
| king | 40 |
| rose | 33 |
| jack | 32 |
| jerry | 31 |
| robin | 30 |
| zorro | 25 |
| tom | 22 |
+----------+---------+
mysql> select * from emp order by emp_age;
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 3 | tom | 22 | 200 |
| 2 | zorro | 25 | 200 |
| 1 | robin | 30 | 200 |
| 4 | jerry | 31 | 100 |
| 5 | jack | 32 | 100 |
| 6 | rose | 33 | 100 |
| 7 | king | 40 | 300 |
| 8 | jean | 41 | 300 |
| 9 | natasha | 42 | 300 |
| 10 | xinghh | 43 | 300 |
| 11 | xingxx | 43 | 300 |
+--------+----------+---------+--------+
limit 选择行数限制
mysql> select * from emp limit 5; # 选择前五行
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 1 | robin | 30 | 200 |
| 2 | zorro | 25 | 200 |
| 3 | tom | 22 | 200 |
| 4 | jerry | 31 | 100 |
| 5 | jack | 32 | 100 |
+--------+----------+---------+--------+
mysql> select * from emp limit 3,5; # 选择第三行以后五行
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 4 | jerry | 31 | 100 |
| 5 | jack | 32 | 100 |
| 6 | rose | 33 | 100 |
| 7 | king | 40 | 300 |
| 8 | jean | 41 | 300 |
+--------+----------+---------+--------+
mysql> select * from emp order by emp_age limit 1; # emp_age 年纪最年轻的
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 3 | tom | 22 | 200 |
+--------+----------+---------+--------+
mysql> select * from emp order by emp_age desc limit 1; # 年纪最大的
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 11 | xingxx | 43 | 300 |
+--------+----------+---------+--------+
mysql> select emp_age from emp order by emp_age desc limit 1;
+---------+
| emp_age |
+---------+
| 43 |
+---------+
where子句
mysql> select * from emp where emp_name='zorro';
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 2 | zorro | 25 | 200 |
+--------+----------+---------+--------+
where 子句操作符
mysql> select * from emp where emp_age>30;
mysql> select * from emp where emp_age>=30;
mysql> select * from emp where emp_age between 30 and 33; # 30-33岁之间的
·······
mysql> select * from emp where emp_age!=30; # 不等于
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 2 | zorro | 25 | 200 |
| 3 | tom | 22 | 200 |
| 4 | jerry | 31 | 100 |
| 5 | jack | 32 | 100 |
| 6 | rose | 33 | 100 |
| 7 | king | 40 | 300 |
| 8 | jean | 41 | 300 |
| 9 | natasha | 42 | 300 |
| 10 | xinghh | 43 | 300 |
| 11 | xingxx | 43 | 300 |
+--------+----------+---------+--------+
mysql> select * from emp where emp_age<>30; # <> 不等于
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 2 | zorro | 25 | 200 |
| 3 | tom | 22 | 200 |
| 4 | jerry | 31 | 100 |
| 5 | jack | 32 | 100 |
| 6 | rose | 33 | 100 |
| 7 | king | 40 | 300 |
| 8 | jean | 41 | 300 |
| 9 | natasha | 42 | 300 |
| 10 | xinghh | 43 | 300 |
| 11 | xingxx | 43 | 300 |
+--------+----------+---------+--------+
mysql> select * from emp where emp_age is null;
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 11 | xingnull | NULL | 300 |
+--------+----------+---------+--------+
mysql> select * from emp where emp_age is not null
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 1 | robin | 30 | 200 |
···
| 10 | xinghh | 43 | 300 |
| 11 | xingxx | 43 | 300 |
+--------+----------+---------+--------+
mysql> select * from emp where emp_age >35 and emp_age<=40;
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 7 | king | 40 | 300 |
+--------+----------+---------+--------+
1 row in set (0.01 sec)mysql> select * from emp where emp_age >35 or emp_age <=40;
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 1 | robin | 30 | 200 |
···
| 10 | xinghh | 43 | 300 |
| 11 | xingxx | 43 | 300 |
+--------+----------+---------+--------+
mysql> select * from emp where dpt_id=200 or dpt_id=100 and emp_age>24; # or操作符表示满足任意条件 或操作
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 1 | robin | 30 | 200 |
| 2 | zorro | 25 | 200 |
| 3 | tom | 22 | 200 |
| 4 | jerry | 31 | 100 |
| 5 | jack | 32 | 100 |
| 6 | rose | 33 | 100 |
+--------+----------+---------+--------+
mysql> select * from emp where (dpt_id=200 or dpt__id=100) and emp_age>24; # and 优先执行；and操作符表示两个条件都要满足 与操作
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 1 | robin | 30 | 200 |
| 2 | zorro | 25 | 200 |
| 4 | jerry | 31 | 100 |
| 5 | jack | 32 | 100 |
| 6 | rose | 33 | 100 |
+--------+----------+---------+--------+
mysql> select * from emp where dpt_id=100 or dpt_id=200 or dpt_id =300;
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 1 | robin | 30 | 200 |
···
| 10 | xinghh | 43 | 300 |
| 11 | xingxx | 43 | 300 |
| 11 | xingnull | NULL | 300 |
+--------+----------+---------+--------+
mysql> select * from emp where dpt_id in (100,200,300);
1.in的语法更加直观
2.in的计算次序更容易管理(操作符少)
3.in 一般比or执行的更快
4.in的最大优点可以包含其他子句 or不行
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 1 | robin | 30 | 200 |
···
| 9 | natasha | 42 | 300 |
| 10 | xinghh | 43 | 300 |
| 11 | xingxx | 43 | 300 |
| 11 | xingnull | NULL | 300 |
+--------+----------+---------+--------+
mysql> select * from emp where emp_name like "j%n"; # 通配符%匹配多个字符_匹配一个字符
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 8 | jean | 41 | 300 |
+--------+----------+---------+--------+
mysql> select * from emp where emp_name like "j_n";
Empty set (0.00 sec)
mysql> select * from emp where emp_name regexp "je*";
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 4 | jerry | 31 | 100 |
| 5 | jack | 32 | 100 |
| 8 | jean | 41 | 300 |
+--------+----------+---------+--------+
计算
mysql> insert into emp values(11,'xingnull',null,300);
mysql> select * from emp;
+--------+----------+---------+--------+
| emp_id | emp_name | emp_age | dpt_id |
+--------+----------+---------+--------+
| 1 | robin | 30 | 200 |
···
| 11 | xingxx | 43 | 300 |
| 11 | xingnull | NULL | 300 |
+--------+----------+---------+--------+
mysql> select emp_age*100*12+5000 from emp;
+---------------------+
| emp_age*100*12+5000 |
+---------------------+
| 41000 |
| 35000 |
····
| 56600 |
| NULL |
+---------------------+
mysql> select emp_age*100*12+5000 from emp; # 可以用来计算
+---------------------+
| emp_age*100*12+5000 |
+---------------------+
| 41000 |
····
| 56600 |
| NULL |
+---------------------+
mysql> select concat(emp_id,emp_name)from emp;
+-------------------------+
| concat(emp_id,emp_name) |
+-------------------------+
| 1robin |
····
| 11xingxx |
| 11xingnull |
+-------------------------+
mysql> select upper(emp_name) from emp;
+-----------------+
| upper(emp_name) |
+-----------------+
| ROBIN |
····
| XINGNULL |
+-----------------+
mysql> select lower(upper(emp_name)) from emp;
+------------------------+
| lower(upper(emp_name)) |
+------------------------+
| robin |
····
| natasha |
| xinghh |
| xingxx |
| xingnull |
+------------------------+
mysql> select max(emp_age)from emp;
+--------------+
| max(emp_age) |
+--------------+
| 43 |
+--------------+
mysql> select min(emp_age)from emp;
+--------------+
| min(emp_age) |
+--------------+
| 22 |
+--------------+
mysql> select avg(emp_age)from emp;
+--------------+
| avg(emp_age) |
+--------------+
| 34.7273 |
+--------------+
mysql> select sum(emp_age*100*6+299-67)from emp; # 求和
+---------------------------+
| sum(emp_age*100*6+299-67) |
+---------------------------+
| 231752 |
+---------------------------+
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
mysql> select * from t11;
+--------+
| myyear |
+--------+
| 2023 |
| 2023 |
| 1970 |
| 2060 |
| 1999 |
+--------+
mysql> select month(mydt)from t12;
+-------------+
| month(mydt) |
+-------------+
| 8 |
| 8 |
+-------------+
mysql> select day(mydt)from t12;
+-----------+
| day(mydt) |
+-----------+
| 16 |
| 16 |
+-----------+
mysql> select hour(mydt)from t12;
+------------+
| hour(mydt) |
+------------+
| 10 |
| 10 |
+------------+
mysql> select minute(mydt)from t12;
+--------------+
| minute(mydt) |
+--------------+
| 10 |
| 10 |
+--------------+
mysql> select second(mydt)from t12;
+--------------+
| second(mydt) |
+--------------+
| 10 |
| 10 |
+--------------+
mysql> select count(*) from emp group by dpt_id;
+----------+
| count(*) |
+----------+
| 3 |
| 3 |
| 6 |
+----------+
mysql> select count(*)as new from emp group by dpt_id having new>5;
+-----+
| new |
+-----+
| 6 |
+-----+
mysql> select count(*)as new from emp group by dpt_id having new>1;
+-----+
| new |
+-----+
| 3 |
| 3 |
| 6 |
+-----+
mysql> create table cpu(prod_name char(10),prod_pris int,vender_name char(10),prod_date date);
mysql> insert into cpu values('cpu1',1200,'inter','2018-10-10');
······
mysql> insert into cpu values('cpu5',2800,'amd','2018-10-10');
mysql> select * from cpu;
+-----------+-----------+-------------+------------+
| prod_name | prod_pris | vender_name | prod_date |
+-----------+-----------+-------------+------------+
| cpu1 | 1200 | inter | 2018-10-10 |
| cpu2 | 800 | amd | 2017-10-10 |
| cpu3 | 1500 | inter | 2018-10-10 |
| cpu4 | 1800 | amd | 2018-10-10 |
| cpu5 | 2800 | amd | 2018-10-10 |
+-----------+-----------+-------------+------------+
mysql> select max(prod_pris) from cpu;
+----------------+
| max(prod_pris) |
+----------------+
| 2800 |
+----------------+
mysql> select max(prod_pris) from cpu group by vender_name;
+----------------+
| max(prod_pris) |
+----------------+
+----------------+
| 2800 |
| 1500 |
+----------------+
mysql> select prod_name,prod_pris from cpu,(select max(prod_pris) as new_pris from cpu group by vender_name) as new where cpu.prod_pris=new.new_pris;
+-----------+-----------+
| prod_name | prod_pris |
+-----------+-----------+
| cpu3 | 1500 |
| cpu5 | 2800 |
+-----------+-----------+
mysql> select prod_name,prod_pris,vender_name from cpu,(select max(prod_pris) as new_pris from cpu group by vender_name) as new where cpu.prod_pris=new.new_pris;
+-----------+-----------+-------------+
| prod_name | prod_pris | vender_name |
+-----------+-----------+-------------+
| cpu3 | 1500 | inter |
| cpu5 | 2800 | amd |
+-----------+-----------+-------------+
mysql> select prod_name,prod_pris,vender_name from cpu,(select max(prod_pris) as new_pris,vender_name as new_name from cpu group by vender_name) as new where cpu.prod_pris=new.new_pris;
+-----------+-----------+-------------+
| prod_name | prod_pris | vender_name |
+-----------+-----------+-------------+
| cpu3 | 1500 | inter |
| cpu5 | 2800 | amd |
+-----------+-----------+-------------+
创建一个emp/dpt表
mysql> create table emp(emp_id int,emp_name char(10),emp_age tinyint, dpt_id int); # 创建表 emp,插入数据
mysql> insert into emp values(1,'robin',30,200);
······
mysql> insert into emp values(9,'natasha',42,300);
mysql> insert into emp values(10,'xinghh',43,300);
mysql> desc emp;
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| emp_id | int(11) | YES | | NULL | |
| emp_name | char(10) | YES | | NULL | |
| emp_age | tinyint(4) | YES | | NULL | |
| dpt_id | int(11) | YES | | NULL | |
+----------+------------+------+-----+---------+-------+
mysql> alter table emp add emp_money int; # add 添加一列内容
mysql> update emp set emp_money=10000 where emp_id=1;
·····
mysql> update emp set emp_money=90000 where emp_id=9;
mysql> update emp set emp_money=100000 where emp_id=10;
mysql> select * from emp;
+--------+----------+---------+--------+-----------+
| emp_id | emp_name | emp_age | dpt_id | emp_money |
+--------+----------+---------+--------+-----------+
| 1 | robin | 30 | 200 | 10000 |
| 2 | zorro | 25 | 200 | 20000 |
| 3 | tom | 22 | 200 | 30000 |
| 4 | jerry | 31 | 100 | 40000 |
| 5 | jack | 32 | 100 | 50000 |
| 6 | rose | 33 | 100 | 60000 |
| 7 | king | 40 | 300 | 70000 |
| 8 | jean | 41 | 300 | 80000 |
| 9 | natasha | 42 | 300 | 90000 |
| 10 | xinghh | 43 | 300 | 100000 |
+--------+----------+---------+--------+-----------+
mysql> create table dpt(dpt_id int,dpt_name char(10)); # 创建表 dpt,插入数据
mysql> insert into dpt values(100,'yw');
mysql> insert into dpt values(200,'kf');
······
mysql> insert into dpt values(400,'boss');
mysql> desc dpt;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| dpt_id | int(11) | YES | | NULL | |
| dpt_name | char(10) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
mysql> select * from dpt;
+--------+----------+
| dpt_id | dpt_name |
+--------+----------+
| 100 | yw |
| 200 | kf |
| 300 | hr |
| 400 | boss |
+--------+----------+
PART1:
mysql> select * from emp,dpt;
+--------+----------+---------+--------+-----------+--------+----------+
| emp_id | emp_name | emp_age | dpt_id | emp_money | dpt_id | dpt_name |
+--------+----------+---------+--------+-----------+--------+----------+
| 1 | robin | 30 | 200 | 10000 | 100 | yw |
| 1 | robin | 30 | 200 | 10000 | 200 | kf |
| 1 | robin | 30 | 200 | 10000 | 300 | hr |
| 1 | robin | 30 | 200 | 10000 | 400 | boss |
··········省略
| 10 | xinghh | 43 | 300 | 100000 | 100 | yw |
| 10 | xinghh | 43 | 300 | 100000 | 200 | kf |
| 10 | xinghh | 43 | 300 | 100000 | 300 | hr |
| 10 | xinghh | 43 | 300 | 100000 | 400 | boss |
+--------+----------+---------+--------+-----------+--------+----------+
mysql> select * from emp,dpt where emp.dpt_id = dpt.dpt_id;
+--------+----------+---------+--------+-----------+--------+----------+
| emp_id | emp_name | emp_age | dpt_id | emp_money | dpt_id | dpt_name |
+--------+----------+---------+--------+-----------+--------+----------+
| 1 | robin | 30 | 200 | 10000 | 200 | kf |
| 2 | zorro | 25 | 200 | 20000 | 200 | kf |
| 3 | tom | 22 | 200 | 30000 | 200 | kf |
| 4 | jerry | 31 | 100 | 40000 | 100 | yw |
| 5 | jack | 32 | 100 | 50000 | 100 | yw |
| 6 | rose | 33 | 100 | 60000 | 100 | yw |
| 7 | king | 40 | 300 | 70000 | 300 | hr |
| 8 | jean | 41 | 300 | 80000 | 300 | hr |
| 9 | natasha | 42 | 300 | 90000 | 300 | hr |
| 10 | xinghh | 43 | 300 | 100000 | 300 | hr |
+--------+----------+---------+--------+-----------+--------+----------+
mysql> select * from emp left join dpt on emp.dpt_id =dpt.dpt_id;
+--------+----------+---------+--------+-----------+--------+----------+
| emp_id | emp_name | emp_age | dpt_id | emp_money | dpt_id | dpt_name |
+--------+----------+---------+--------+-----------+--------+----------+
| 4 | jerry | 31 | 100 | 40000 | 100 | yw |
| 5 | jack | 32 | 100 | 50000 | 100 | yw |
| 6 | rose | 33 | 100 | 60000 | 100 | yw |
| 1 | robin | 30 | 200 | 10000 | 200 | kf |
| 2 | zorro | 25 | 200 | 20000 | 200 | kf |
| 3 | tom | 22 | 200 | 30000 | 200 | kf |
| 7 | king | 40 | 300 | 70000 | 300 | hr |
| 8 | jean | 41 | 300 | 80000 | 300 | hr |
| 9 | natasha | 42 | 300 | 90000 | 300 | hr |
| 10 | xinghh | 43 | 300 | 100000 | 300 | hr |
+--------+----------+---------+--------+-----------+--------+----------+
mysql> select * from emp right join dpt on emp.dpt_id =dpt.dpt_id;
+--------+----------+---------+--------+-----------+--------+----------+
| emp_id | emp_name | emp_age | dpt_id | emp_money | dpt_id | dpt_name |
+--------+----------+---------+--------+-----------+--------+----------+
| 1 | robin | 30 | 200 | 10000 | 200 | kf |
| 2 | zorro | 25 | 200 | 20000 | 200 | kf |
| 3 | tom | 22 | 200 | 30000 | 200 | kf |
| 4 | jerry | 31 | 100 | 40000 | 100 | yw |
| 5 | jack | 32 | 100 | 50000 | 100 | yw |
| 6 | rose | 33 | 100 | 60000 | 100 | yw |
| 7 | king | 40 | 300 | 70000 | 300 | hr |
| 8 | jean | 41 | 300 | 80000 | 300 | hr |
| 9 | natasha | 42 | 300 | 90000 | 300 | hr |
| 10 | xinghh | 43 | 300 | 100000 | 300 | hr |
| NULL | NULL | NULL | NULL | NULL | 400 | boss |
+--------+----------+---------+--------+-----------+--------+----------+
mysql> select * from dpt left join emp on emp.dpt_id=dpt.dpt_id;
+--------+----------+--------+----------+---------+--------+-----------+
| dpt_id | dpt_name | emp_id | emp_name | emp_age | dpt_id | emp_money |
+--------+----------+--------+----------+---------+--------+-----------+
| 200 | kf | 1 | robin | 30 | 200 | 10000 |
| 200 | kf | 2 | zorro | 25 | 200 | 20000 |
| 200 | kf | 3 | tom | 22 | 200 | 30000 |
| 100 | yw | 4 | jerry | 31 | 100 | 40000 |
| 100 | yw | 5 | jack | 32 | 100 | 50000 |
| 100 | yw | 6 | rose | 33 | 100 | 60000 |
| 300 | hr | 7 | king | 40 | 300 | 70000 |
| 300 | hr | 8 | jean | 41 | 300 | 80000 |
| 300 | hr | 9 | natasha | 42 | 300 | 90000 |
| 300 | hr | 10 | xinghh | 43 | 300 | 100000 |
| 400 | boss | NULL | NULL | NULL | NULL | NULL |
+--------+----------+--------+----------+---------+--------+-----------+
PART2:
mysql> select * from emp where emp_money=(select emp_money from emp order by emp_money desc limit 1);
+--------+----------+---------+--------+-----------+
| emp_id | emp_name | emp_age | dpt_id | emp_money |
+--------+----------+---------+--------+-----------+
| 10 | xinghh | 43 | 300 | 100000 |
+--------+----------+---------+--------+-----------+
mysql> select max(emp_money) from emp;
+----------------+
| max(emp_money) |
+----------------+
| 100000 |
+----------------+
mysql> select max(emp_money) from emp group by dpt_id;
+----------------+
| max(emp_money) |
+----------------+
| 60000 |
| 30000 |
| 100000 |
+----------------+
mysql> select emp_name,emp_money from emp,(select max(emp_money) as new_money from emp group by dpt_id) as new where emp.emp_money =new.new_money;
+----------+-----------+
| emp_name | emp_money |
+----------+-----------+
| tom | 30000 |
| rose | 60000 |
| xinghh | 100000 |
+----------+-----------+
存储引擎：show engines\G
研究大量的临时数据，也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据
mysql> show engines\G
*************************** 1. row ***************************
Engine: InnoDB
*************************** 2. row ***************************
Engine: MRG_MYISAM
*************************** 3. row ***************************
Engine: MEMORY
*************************** 4. row ***************************
Engine: BLACKHOLE
*************************** 5. row ***************************
Engine: MyISAM
*************************** 6. row ***************************
Engine: CSV
*************************** 7. row ***************************
Engine: ARCHIVE
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
*************************** 9. row ***************************
Engine: FEDERATED
#### MEMORY引擎
mysql> create table myt1(id int,name char(10)) engine=memory; #创建memory引擎表
mysql> insert into myt1 values(1,'haxi');
mysql> select * from myt1;
+------+------+
| id | name |
+------+------+
| 1 | haxi |
+------+------+
#### BLACKHOLE 引擎
mysql> create table myt2(id int,name char(10)) engine=blackhole; #BLACKHOLE引擎
mysql> insert into myt2 values (1,'aria');
mysql> select * from myt2;
Empty set (0.00 sec)
#### CSV 引擎
mysql> create table myt3(id int,name char(10)) engine=CSV;
#### MyISAM 引擎
mysql> create table myt4(id int,name char(10)) engine=myisam; # MyISAM引擎，创建基表myt4
mysql> show create table myt4;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------+
| myt4 | CREATE TABLE `myt4` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------+
#### MRG_MYISAM 引擎
mysql> use ascd;
Database changed
mysql> create table t1(id int,name char(10)) engine=myisam;
mysql> create table t2(id int,name char(10)) engine=myisam;
mysql> insert into t1 values(1,'xixi');
mysql> insert into t2 values(2,'haha');
mysql> create table untab(id int,name char(10)) union=(t1,t2) engine=mrg_myisam; # MRG表指定插入表
mysql> select * from untab;
+------+------+
| id | name |
+------+------+
| 1 | xixi |
| 2 | haha |
+------+------+
[root@localhost data]# cd ascd/
[root@localhost ascd]# ls
db.opt t1.MYD t2.frm t2.MYI untab.MRG
t1.frm t1.MYI t2.MYD untab.frm
mysql> update untab set id=100 where id=2; # 更新id
mysql> select * from untab;
+------+------+
| id | name |
+------+------+
| 1 | xixi |
| 100 | haha |
+------+------+
mysql> drop table untab;
mysql> create table untab(id int,name char(10)) union=(t1,t2) insert_method=last engine=mrg_myisam; # insert_method=last
mysql> select * from untab;
+------+------+
| id | name |
+------+------+
| 1 | xixi |
| 100 | haha |
+------+------+
mysql> insert into untab values(20,'jack');
mysql> select * from untab;
+------+------+
| id | name |
+------+------+
| 1 | xixi |
| 100 | haha |
| 20 | jack |
+------+------+
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# mkdir xxx # 创建的文件，但是在MySQL中并不能使用
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ascd |
| hahaha |
| mysql |
| performance_schema |
| sys |
| xxx |
+--------------------+
mysql> use xxx;
Database changed
mysql> create table t1(id int);
ERROR 1005 (HY000): Can't create table 't1' (errno: 13)
[root@localhost data]# rm -rf xxx
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ascd |
| hahaha |
| mysql |
| performance_schema |
| sys |
+--------------------+
