Sql 基础

来自linux中国网wiki
docker>Evan2019年6月28日 (五) 15:07的版本 （→‎2.2.3 DML）
(差异) ←上一版本 | 最后版本 (差异) | 下一版本→ (差异)
跳到导航 跳到搜索 
sql 基础

2.2.1 SQL分类

DDL  DML  DCL
DML（data manipulation language）：数据操纵语言
       它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言
DDL（data definition language）：数据定义语言
       DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
DCL（Data Control Language）：数据控制语言
       是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

2.2.2 DDL

1.创建数据库
 create database test1 character set utf8;

MariaDB [(none)]> use test1;
Database changed
MariaDB [test1]> show tables;
Empty set (0.001 sec)

2. 删除数据库

MariaDB [mysql]> drop database test1;
Query OK, 0 rows affected (0.001 sec)

drop 语句都是显示为0 rows affected (0.001 sec)

3. 创建表
 create table emp(ename varchar(10), hiredate date,sal decimal(10,2),deptno int(20));

MariaDB [test1]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(20)       | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.004 sec)

MariaDB [test1]> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.000 sec)

4. 删除表
drop table tablename;

5. 修改表
MariaDB [test1]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

MariaDB [test1]> alter table  emp modify ename varchar(20);

MariaDB [test1]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |


(2)增加字段  

MariaDB [test1]> alter table emp add column age int(3);

MariaDB [test1]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |

| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+


(3)删除字段
alter table tablename drop  column col_name;

(4) 字段改名
alter  table emp change age age1  int(4);

change modify 的区别p 33 

(5) 修改字段排列顺序

alter table emp add birthd after ename;
 alter table emp modify age int(3) first;

(6) 更改表名 
alter table emp rename emp1;

2.2.3 DML

1. 插入记录
insert into emp(ename,hiredate,sal,deptno) values('evan','2018-01-01','2333',1);

MariaDB [test1]> insert into emp(ename,hiredate,sal,deptno) values('hen','2017-09-08','3333',3);
Query OK, 1 row affected (0.011 sec)

MariaDB [test1]> insert into emp(ename,hiredate,sal,deptno) values('lisa','2017-07-03','3433',2);
Query OK, 1 row affected (0.015 sec)

MariaDB [test1]> insert into emp(ename,hiredate,sal,deptno) values('gjg','2014-07-06','4433',1);
Query OK, 1 row affected (0.005 sec)




MariaDB [test1]> select * from emp;
+-------+------------+---------+--------+------+
| ename | hiredate   | sal     | deptno | age  |
+-------+------------+---------+--------+------+
| evan  | 2018-01-01 | 2333.00 |      1 | NULL |
+-------+------------+---------+--------+------+

可以一次插入多条记录

2.更新记录

MariaDB [test1]> update  emp set sal=8000 where ename='evan';
Query OK, 1 row affected (0.017 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test1]> select * from emp;
+-------+------------+---------+--------+------+
| ename | hiredate   | sal     | deptno | age  |
+-------+------------+---------+--------+------+
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |
+-------+------------+---------+--------+------+

3. 删除记录
drop from emp where ename='evan';


4. 查询记录
(2) 条件查询
MariaDB [test1]> select * from emp where ename='evan';
+-------+------------+---------+--------+------+
| ename | hiredate   | sal     | deptno | age  |
+-------+------------+---------+--------+------+
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |

(3) 排序和限制
MariaDB [test1]> select * from emp order by sal; 
+-------+------------+---------+--------+------+
| ename | hiredate   | sal     | deptno | age  |
+-------+------------+---------+--------+------+
| hen   | 2017-09-08 | 3333.00 |      3 | NULL |
| lisa  | 2017-07-03 | 3433.00 |      2 | NULL |
| gjg   | 2014-07-06 | 4433.00 |      1 | NULL |
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |
+-------+------------+---------+--------+------+

MariaDB [test1]> select * from emp order by sal  limit 1,3; #从第二条记录开始 ，显示屏条
+-------+------------+---------+--------+------+
| ename | hiredate   | sal     | deptno | age  |
+-------+------------+---------+--------+------+
| lisa  | 2017-07-03 | 3433.00 |      2 | NULL |
| gjg   | 2014-07-06 | 4433.00 |      1 | NULL |
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |
+-------+------------+---------+--------+------+

(4)聚合

MariaDB [test1]> select count(1) from emp ;
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.000 sec)

MariaDB [test1]> select  sum(sal),max(sal),min(sal) from emp;
+----------+----------+----------+
| sum(sal) | max(sal) | min(sal) |
+----------+----------+----------+
| 19199.00 |  8000.00 |  3333.00 |
+----------+----------+----------+

统计各个部门的人数
select  count(id)  from emp group by deptno;

(5) 表连接

create table dept(deptno int(8),deptname varchar(10));

insert into dept(deptno,deptname) values(1,'tech');
insert into dept(deptno,deptname) values(2,'sale');
insert into dept(deptno,deptname) values(3,'hr');

MariaDB [test1]> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
所有员工的名字和所有的部门名称 
MariaDB [test1]> select ename,deptname from emp , dept where emp.deptno=dept.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| evan  | tech     |
| hen   | hr       |
| lisa  | sale     |
| gjg   | tech     |
+-------+----------+

(6)子查询

(7) 记录查询

2.2.4 DCL 语句

grant revoke

2.3 use help

MariaDB [test1]> ? contents 
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation

 想知道int类型的相关
MariaDB [test1]> ? int
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

URL: https://mariadb.com/kb/en/int/



MariaDB [test1]> ? show 
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS


MariaDB [test1]> ? create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

see also

DQL、DML、DDL、DCL的概念与区别

DML、DDL、DCL区别

取自“http://wiki.linuxchina.net/index.php?title=Sql_基础&oldid=1076”