数据库练习

[root@localhost /]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.61-log MySQL Community Server (GPL)

Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| stumysql |
| stumysql1 |
| test |
±-------------------+
6 rows in set (0.00 sec)

mysql>
mysql> create database stumysql2;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| stumysql |
| stumysql1 |
| stumysql2 |
| test |
±-------------------+
7 rows in set (0.00 sec)

mysql>
mysql> use stumysql2;
Database changed
mysql> create table student(
-> sid int primary key auto_increment,
-> snam varchar(30),
-> age int ,
-> rxrq date
-> );
Query OK, 0 rows affected (0.19 sec)

mysql> insert into student(snam,age,rxrq) values(‘lzy’,18,‘2017-09-01’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
±----±-----±-----±-----------+
| sid | snam | age | rxrq |
±----±-----±-----±-----------+
| 1 | lzy | 18 | 2017-09-01 |
±----±-----±-----±-----------+
1 row in set (0.00 sec)

mysql> insert into student(snam,age,rxrq) values(‘myq’,21,‘2017-09-01’),(‘lcb’,20,‘2017-09-01),(‘syc’,19,2017-09-01’);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘syc’,19,2017-09-01’)’ at line 1
mysql> insert into student(snam,age,rxrq) values(‘myq’,21,‘2017-09-01’),(‘lcb’,20,‘2017-09-01’),(‘syc’,19,2017-09-01’);
'> ;
'> insert into student(snam,age,rxrq) values(‘myq’,21,‘2017-09-01’),(‘lcb’,20,‘2017-09-01’),(‘syc’,19,‘2017-09-01’);
'>
'> ;
'> Ctrl-C – exit!
Aborted
[root@localhost /]# mysql
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
[root@localhost /]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.61-log MySQL Community Server (GPL)

Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> use stumysql2;
Database changed
mysql> select * from student;
±----±-----±-----±-----------+
| sid | snam | age | rxrq |
±----±-----±-----±-----------+
| 1 | lzy | 18 | 2017-09-01 |
±----±-----±-----±-----------+
1 row in set (0.00 sec)

mysql> insert into student(snam,age,rxrq) values(‘myq’,21,‘2017-09-01’),(‘lcb’,20,‘2017-09-01’),(‘syc’,19,‘2017-09-01’);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
±----±-----±-----±-----------+
| sid | snam | age | rxrq |
±----±-----±-----±-----------+
| 1 | lzy | 18 | 2017-09-01 |
| 2 | myq | 21 | 2017-09-01 |
| 3 | lcb | 20 | 2017-09-01 |
| 4 | syc | 19 | 2017-09-01 |
±----±-----±-----±-----------+
4 rows in set (0.00 sec)

mysql> update student set age=20 where snam=‘myq’
-> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from student;
±----±-----±-----±-----------+
| sid | snam | age | rxrq |
±----±-----±-----±-----------+
| 1 | lzy | 18 | 2017-09-01 |
| 2 | myq | 20 | 2017-09-01 |
| 3 | lcb | 20 | 2017-09-01 |
| 4 | syc | 19 | 2017-09-01 |
±----±-----±-----±-----------+
4 rows in set (0.00 sec)

mysql> delete from student where sid=4;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
±----±-----±-----±-----------+
| sid | snam | age | rxrq |
±----±-----±-----±-----------+
| 1 | lzy | 18 | 2017-09-01 |
| 2 | myq | 20 | 2017-09-01 |
| 3 | lcb | 20 | 2017-09-01 |
±----±-----±-----±-----------+
3 rows in set (0.00 sec)

mysql>
mysql> create table dept(
-> did int primary key auto_increment,
-> dnam varchar(30),
-> dleader varchar(30)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> create table emp(
-> eid int primary key auto_increment,
-> enam varchar(20),
-> did int,
-> eage int,
-> gzsj date,
-> job varchar(30),
-> sar decimal(14,4)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dept(dnam, dleader) (‘xsb’,‘zy’),(‘cwb’,‘wxx’),(‘rsb’,‘gpw’);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘xsb’,‘zy’),(‘cwb’,‘wxx’),(‘rsb’,‘gpw’)’ at line 1
mysql> insert into dept(dnam, dleader) values(‘xsb’,‘zy’),(‘cwb’,‘wxx’),(‘rsb’,‘gpw’);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql>
mysql> insert into emp(enam,did,eage,gzsj,job,sar) values(‘qxt’,1,18,‘2018-07-01’,‘wu’,6500);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp(enam,did,eage,gzsj,job,sar) values(‘lsh’,2,19,‘2017-07-01’,‘wu’,6600);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp(enam,did,eage,gzsj,job,sar) values(‘cjw’,3,19,‘2017-07-01’,‘wu’,6800);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp(enam,did,eage,gzsj,job,sar) values(‘zsz’,1,19,‘2018-09-01’,‘wu’,6080);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from dept;
±----±-----±--------+
| did | dnam | dleader |
±----±-----±--------+
| 1 | xsb | zy |
| 2 | cwb | wxx |
| 3 | rsb | gpw |
±----±-----±--------+
3 rows in set (0.00 sec)

mysql> select * from emp;
±----±-----±-----±-----±-----------±-----±----------+
| eid | enam | did | eage | gzsj | job | sar |
±----±-----±-----±-----±-----------±-----±----------+
| 1 | qxt | 1 | 18 | 2018-07-01 | wu | 6500.0000 |
| 2 | lsh | 2 | 19 | 2017-07-01 | wu | 6600.0000 |
| 3 | cjw | 3 | 19 | 2017-07-01 | wu | 6800.0000 |
| 4 | zsz | 1 | 19 | 2018-09-01 | wu | 6080.0000 |
±----±-----±-----±-----±-----------±-----±----------+
4 rows in set (0.00 sec)

mysql> Ctrl-C – exit!
Aborted
[root@localhost /]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.61-log MySQL Community Server (GPL)

Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> select * from dept;
ERROR 1046 (3D000): No database selected
mysql> use stumysql2;
Database changed
mysql>
mysql> #需要给表Department增加一列字段notes，长度为10的字符串，默认值为‘0’ , 请写出相关SQL语句
mysql> alter table dept add notes varchar(10) default ‘0’;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from dept;
±----±-----±--------±------+
| did | dnam | dleader | notes |
±----±-----±--------±------+
| 1 | xsb | zy | 0 |
| 2 | cwb | wxx | 0 |
| 3 | rsb | gpw | 0 |
±----±-----±--------±------+
3 rows in set (0.00 sec)

mysql> #查找工资大于2000元的员工记录，并按员工号id升序排列
mysql> 答：
->
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘答：’ at line 1
mysql> select * from emp where sar>6100 order by eage asc;
±----±-----±-----±-----±-----------±-----±----------+
| eid | enam | did | eage | gzsj | job | sar |
±----±-----±-----±-----±-----------±-----±----------+
| 1 | qxt | 1 | 18 | 2018-07-01 | wu | 6500.0000 |
| 2 | lsh | 2 | 19 | 2017-07-01 | wu | 6600.0000 |
| 3 | cjw | 3 | 19 | 2017-07-01 | wu | 6800.0000 |
±----±-----±-----±-----±-----------±-----±----------+
3 rows in set (0.00 sec)

mysql> select * from emp where sar>6100 order by eage desc;
±----±-----±-----±-----±-----------±-----±----------+
| eid | enam | did | eage | gzsj | job | sar |
±----±-----±-----±-----±-----------±-----±----------+
| 2 | lsh | 2 | 19 | 2017-07-01 | wu | 6600.0000 |
| 3 | cjw | 3 | 19 | 2017-07-01 | wu | 6800.0000 |
| 1 | qxt | 1 | 18 | 2018-07-01 | wu | 6500.0000 |
±----±-----±-----±-----±-----------±-----±----------+
3 rows in set (0.00 sec)

mysql> select * from emp where sar>6100 order by eage desc,enam asc;
±----±-----±-----±-----±-----------±-----±----------+
| eid | enam | did | eage | gzsj | job | sar |
±----±-----±-----±-----±-----------±-----±----------+
| 3 | cjw | 3 | 19 | 2017-07-01 | wu | 6800.0000 |
| 2 | lsh | 2 | 19 | 2017-07-01 | wu | 6600.0000 |
| 1 | qxt | 1 | 18 | 2018-07-01 | wu | 6500.0000 |
±----±-----±-----±-----±-----------±-----±----------+
3 rows in set (0.00 sec)

mysql>
mysql> #查找工资大于2000元的员工所在部门、部门编号、部门经理、员工名称
mysql> select * from dept d,emp e where d.did=e.did;
±----±-----±--------±------±----±-----±-----±-----±-----------±-----±----------+
| did | dnam | dleader | notes | eid | enam | did | eage | gzsj | job | sar |
±----±-----±--------±------±----±-----±-----±-----±-----------±-----±----------+
| 1 | xsb | zy | 0 | 1 | qxt | 1 | 18 | 2018-07-01 | wu | 6500.0000 |
| 2 | cwb | wxx | 0 | 2 | lsh | 2 | 19 | 2017-07-01 | wu | 6600.0000 |
| 3 | rsb | gpw | 0 | 3 | cjw | 3 | 19 | 2017-07-01 | wu | 6800.0000 |
| 1 | xsb | zy | 0 | 4 | zsz | 1 | 19 | 2018-09-01 | wu | 6080.0000 |
±----±-----±--------±------±----±-----±-----±-----±-----------±-----±----------+
4 rows in set (0.00 sec)

mysql> select dnam,e.did,dleader,enam from dept d,emp e where d.did=e.did and sar>2000
-> ;
±-----±-----±--------±-----+
| dnam | did | dleader | enam |
±-----±-----±--------±-----+
| xsb | 1 | zy | qxt |
| cwb | 2 | wxx | lsh |
| rsb | 3 | gpw | cjw |
| xsb | 1 | zy | zsz |
±-----±-----±--------±-----+
4 rows in set (0.00 sec)

mysql> create table stt(
-> sid int primary key,
-> snam varchar(20)
-> ) default character set utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into stt(snam) values(‘张三’)；
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘；’ at line 1
mysql> insert into stt(snam) values(‘张三’);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql>
mysql> select * from stt;
±----±-------+
| sid | snam |
±----±-------+
| 0 | 张三 |
±----±-------+
1 row in set (0.00 sec)

mysql>
mysql> #查找张三和李四所在部门所有人员的姓名
mysql> select * from emp;
±----±-----±-----±-----±-----±-----±----------+
| eid | enam | did | gzjl | job | zc | sar |
±----±-----±-----±-----±-----±-----±----------+
| 1 | hx | 1 | wu | xszy | zj | 6000.0000 |
| 2 | lww | 1 | wu | xszy | zj | 6500.0000 |
| 3 | gwg | 2 | wu | rszy | zj | 6500.0000 |
| 4 | wb | 3 | wu | cw | zj | 8000.0000 |
±----±-----±-----±-----±-----±-----±----------+
4 rows in set (0.00 sec)

mysql> select * from emp where did in(1 ,2);
±----±-----±-----±-----±-----±-----±----------+
| eid | enam | did | gzjl | job | zc | sar |
±----±-----±-----±-----±-----±-----±----------+
| 1 | hx | 1 | wu | xszy | zj | 6000.0000 |
| 2 | lww | 1 | wu | xszy | zj | 6500.0000 |
| 3 | gwg | 2 | wu | rszy | zj | 6500.0000 |
±----±-----±-----±-----±-----±-----±----------+
3 rows in set (0.00 sec)

mysql> select enam from emp where did in((select did from emp where enam=‘lww’),( select did from emp where enam=‘gwg’));
±-----+
| enam |
±-----+
| hx |
| lww |
| gwg |
±-----+
3 rows in set (0.00 sec)

mysql>
mysql> #查看每个部门的部门经理和部门人数，按部门人数排序？
mysql> select * from dept;
±----±-----±-----±------------±-----------------+
| did | dnam | dma | tel | email |
±----±-----±-----±------------±-----------------+
| 1 | xsb | gsy | 18530900286 | 156477295@qq.com |
| 2 | rsb | tn | 18530900286 | 156477295@qq.com |
| 3 | cwb | bfm | 18530900286 | 156477295@qq.com |
±----±-----±-----±------------±-----------------+
3 rows in set (0.00 sec)

mysql> select dma,count(*) 部门人数 from dept d,emp e where d.did=e.did group by e.did;
±-----±-------------+
| dma | 部门人数 |
±-----±-------------+
| gsy | 2 |
| tn | 1 |
| bfm | 1 |
±-----±-------------+
3 rows in set (0.00 sec)

mysql>
mysql> #删除表dept中的所有记录
mysql> delete from dept;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from dept;
Empty set (0.00 sec)

mysql> drop dept;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘dept’ at line 1
mysql> drop table dept;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp limit 0 ,2;
±----±-----±-----±-----±-----±-----±----------+
| eid | enam | did | gzjl | job | zc | sar |
±----±-----±-----±-----±-----±-----±----------+
| 1 | hx | 1 | wu | xszy | zj | 6000.0000 |
| 2 | lww | 1 | wu | xszy | zj | 6500.0000 |
±----±-----±-----±-----±-----±-----±----------+
2 rows in set (0.00 sec)