mysql基础(二)

来源：互联网发布：长春网络维护编辑：程序博客网时间：2024/04/30 00:04

1：查询数据库版本

select version();
+-----------+
| version() |
+-----------+
| 5.6.36 |
+-----------+
1 row in set (0.00 sec)

2:查看当前数据库

select database();
+------------+
| database() |
+------------+
| mydatabase |
+------------+
1 row in set (0.00 sec)

3：终止当前sql语句

create
-> \c

4：推出数据库

exit;ctrl+C

5:查看创建表时的结构

show create table dept;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dept | CREATE TABLE `dept` (
`DEPTNO` int(2) NOT NULL,
`DNAME` varchar(14) DEFAULT NULL,
`LOC` varchar(13) DEFAULT NULL,
PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6:查询数据表中的所有记录

select * from emp;
+-------+---------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME   | JOB       | MGR | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+---------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH   | CLERK     | 7902 | 2017-05-13 | 800.00 |    NULL |     20 |
| 7499 | ALLEN   | SALESMAN | 7698 | 2017-05-13 | 1600.00 | 300.00 |     30 |
| 7521 | WARD    | SALESMAN | 7698 | 2017-05-13 | 1250.00 | 500.00 |     30 |
| 7566 | JONES   | MANAGER   | 7839 | 2017-05-13 | 2975.00 |    NULL |     20 |
| 7654 | MARTIM | SALESMAN | 7698 | 2017-05-13 | 1250.00 | 1400.00 |     30 |
| 7698 | BLAKE   | MANAGER   | 7839 | 2017-05-13 | 2850.00 |    NULL |     30 |
| 7782 | CLARK   | MANAGERAN | 7839 | 2017-05-13 | 2450.00 |    NULL |     10 |
| 7788 | SCOLL   | ANALIST   | 7566 | 2017-05-13 | 3000.00 |    NULL |     20 |
| 7839 | KING    | PRESIDENT | NULL | 2017-05-13 | 5000.00 |    NULL |     10 |
| 7844 | IUSRNER | SALESMAN | 7698 | 2017-05-13 | 1500.00 |    NULL |     30 |
| 7876 | ADAMS   | CLECK     | 7788 | 2017-05-13 | 1100.00 |    NULL |     20 |
| 7900 | JAMES   | CLECK     | 7698 | 2017-05-13 | 950.00 |    NULL |     30 |
| 7902 | FORD    | ANALYST   | 7566 | 2017-05-13 | 3000.00 |    NULL |     20 |
| 7934 | MILLER | CLERY     | 7782 | 2017-05-13 | 1300.00 |    NULL |     10 |
+-------+---------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

7：查询制定记录

select ename,sal from emp;
+---------+---------+
| ename   | sal     |
+---------+---------+
| SMITH   | 800.00 |
| ALLEN   | 1600.00 |
| WARD    | 1250.00 |
| JONES   | 2975.00 |
| MARTIM | 1250.00 |
| BLAKE   | 2850.00 |
| CLARK   | 2450.00 |
| SCOLL   | 3000.00 |
| KING    | 5000.00 |
| IUSRNER | 1500.00 |
| ADAMS   | 1100.00 |
| JAMES   | 950.00 |
| FORD    | 3000.00 |
| MILLER | 1300.00 |
+---------+---------+
14 rows in set (0.00 sec)

8：在sql语句上使用数学表达式

select empno,ename,12*sal from emp;
+-------+---------+----------+
| empno | ename   | 12*sal   |
+-------+---------+----------+
| 7369 | SMITH   | 9600.00 |
| 7499 | ALLEN   | 19200.00 |
| 7521 | WARD    | 15000.00 |
| 7566 | JONES   | 35700.00 |
| 7654 | MARTIM | 15000.00 |
| 7698 | BLAKE   | 34200.00 |
| 7782 | CLARK   | 29400.00 |
| 7788 | SCOLL   | 36000.00 |
| 7839 | KING    | 60000.00 |
| 7844 | IUSRNER | 18000.00 |
| 7876 | ADAMS   | 13200.00 |
| 7900 | JAMES   | 11400.00 |
| 7902 | FORD    | 36000.00 |
| 7934 | MILLER | 15600.00 |
+-------+---------+----------+

9：查询数据表中给临时表中的字段重命名

select empno,ename,12*sal as yearsal from emp;
+-------+---------+----------+
| empno | ename   | yearsal |
+-------+---------+----------+
| 7369 | SMITH   | 9600.00 |
| 7499 | ALLEN   | 19200.00 |
| 7521 | WARD    | 15000.00 |
| 7566 | JONES   | 35700.00 |
| 7654 | MARTIM | 15000.00 |
| 7698 | BLAKE   | 34200.00 |
| 7782 | CLARK   | 29400.00 |
| 7788 | SCOLL   | 36000.00 |
| 7839 | KING    | 60000.00 |
| 7844 | IUSRNER | 18000.00 |
| 7876 | ADAMS   | 13200.00 |
| 7900 | JAMES   | 11400.00 |
| 7902 | FORD    | 36000.00 |
| 7934 | MILLER | 15600.00 |
+-------+---------+----------+
14 rows in set (0.03 sec)

0 0