[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)
