开发者社区> 科技小能手> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL之SQL基础

简介:
+关注继续查看

  SQL是Structure Query Language(结构化查询语言)的缩写,它是关系型数据库的应用语言,由IBM在20世纪70年代开发,以实现关系型数据库中的信息检索。

    在20世纪80年代初,美国国家标准局(ANSI)开始着手制定SQL标准,最早的ANSI标准于1986年完成,就被叫做SQL-86。正是由于SQL语言的标准化,所以大多数关系型数据库都支持SQL语言,它已经发展成为多种平台进行交互操作的底层会话语言。

    SQL的分类:

  • DDL:数据定义语言,即是对数据库内部对象进行创建、删除、修改等操作的语言,和DML最大区别在于DML仅对表内数据进行操作,而不涉及到表的定义、结构的修改,更不会涉及其它对象,DBA使用较多。常用关键字包括create、drop、alter等。

  • DML:数据操作语言,用于添加、删除、更新和查询表中的记录,并检查数据的完整性,开发人员使用较多。常用的语句包括insert、delete、update、和select等。

  • DCL:数据控制语言,用于管理系统中的对象权限时使用,常用语句有grant、revoke等。

1 DDL语句示例:

1)创建数据库

mysql> show engines;                        #查看支持的引擎,包括默认的引擎

+------------+---------+------------------------------------------------------------+--------------+------+------------+

| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |

+------------+---------+------------------------------------------------------------+--------------+------+------------+

| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |

| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |

| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |

| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |

| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |

+------------+---------+------------------------------------------------------------+--------------+------+------------+

mysql> show character set;                   #查看系统支持的字符集

mysql> show variables like "character%";     #查看当前字符集设置

mysql> show variables like "collation%";     #查看字符集校验设置

mysql> create database test1;                #创建test1数据库

Query OK, 1 row affected (0.05 sec)

mysql> show databases;                       #查看数据库

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| test1              |

| testdb             |

mysql> create database t121 default character set utf8 collate utf8_general_ci;

Query OK, 1 row affected (0.00 sec)          #创建时,也可以指定字符集


2)删除数据库

mysql> drop database test1;

Query OK, 0 rows affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| testdb             |

备注:数据库一旦被删除,库中所有的表也将被删除,因此,备份非常重要


3)创建表

mysql> use zwj;                              #选择数据库

mysql> create table emp                      #varchar(n)其中n代表字符数

    -> (ename varchar(10),

    -> hiredate date,

    -> sal decimal(10,2),

    -> deptno int(2));

Query OK, 0 rows affected (0.11 sec)


mysql> desc zwj.emp;                        #查看zwj库中的emp表的结构

+----------+---------------+------+-----+---------+-------+

| Field    | Type          | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename    | varchar(10)   | YES  |     | NULL    |       |

| hiredate | date          | YES  |     | NULL    |       |

| sal      | decimal(10,2) | YES  |     | NULL    |       |

| deptno   | int(2)        | YES  |     | NULL    |       |

+----------+---------------+------+-----+---------+-------+


mysql> show create table zwj.emp\g          #查看创建表的SQL语句,包括使用的字符集

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                     |

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| emp   | CREATE TABLE `emp` (

  `ename` varchar(10) DEFAULT NULL,

  `hiredate` date DEFAULT NULL,

  `sal` decimal(10,2) DEFAULT NULL,

  `deptno` int(2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


复制一张表

mysql> create table t119 like zwj.t118;

Query OK, 0 rows affected (0.04 sec)


mysql> insert into t119 select * from zwj.t118;

Query OK, 12 rows affected (0.00 sec)

Records: 12  Duplicates: 0  Warnings: 0


查看表的状态,了解两张表是否一致

mysql> use zwj;

Database changed

mysql> show table status\G

*************************** 1. row ***************************

           Name: t118

         Engine: MyISAM

        Version: 10

     Row_format: Fixed

           Rows: 12

 Avg_row_length: 30

    Data_length: 360

Max_data_length: 8444249301319679

   Index_length: 1024

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2017-04-29 08:20:18

    Update_time: 2017-04-29 08:29:50

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options: 

        Comment: 

*************************** 2. row ***************************

           Name: t119

         Engine: MyISAM

        Version: 10

     Row_format: Fixed

           Rows: 12

 Avg_row_length: 30

    Data_length: 360

Max_data_length: 8444249301319679

   Index_length: 1024

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2017-04-29 09:09:12

    Update_time: 2017-04-29 09:09:38

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options: 

        Comment: 

2 rows in set (0.00 sec)


查看指定表的状态信息

mysql> show table status like 't118'\G

*************************** 1. row ***************************

           Name: t118

         Engine: MyISAM

        Version: 10

     Row_format: Fixed

           Rows: 12

 Avg_row_length: 30

    Data_length: 360

Max_data_length: 8444249301319679

   Index_length: 1024

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2017-04-29 08:20:18

    Update_time: 2017-04-29 08:29:50

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options: 

        Comment: 

1 row in set (0.00 sec)


4)删除表

mysql> drop table zwj.emp;

Query OK, 0 rows affected (0.05 sec)


5)修改表,需要用到alter table语句

修改表ename字段的定义,把varchar(10)改为varchar(20)

mysql> alter table emp modify ename varchar(20);    #关键字modify用于修改表中字段的定义

Query OK, 0 rows affected (0.17 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field    | Type          | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename    | varchar(20)   | YES  |     | NULL    |       |

| hiredate | date          | YES  |     | NULL    |       |

| sal      | decimal(10,2) | YES  |     | NULL    |       |

| deptno   | int(2)        | YES  |     | NULL    |       |

+----------+---------------+------+-----+---------+-------+


在表emp中新增字段age,类型为int(3):

mysql> alter table emp add age int(3);                #默认排在最后

Query OK, 0 rows affected (0.08 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field    | Type          | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename    | varchar(20)   | YES  |     | NULL    |       |

| hiredate | date          | YES  |     | NULL    |       |

| sal      | decimal(10,2) | YES  |     | NULL    |       |

| deptno   | int(2)        | YES  |     | NULL    |       |

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

+----------+---------------+------+-----+---------+-------+

5 rows in set (0.00 sec)


删除一个字段

mysql> alter table emp drop age;

Query OK, 0 rows affected (0.08 sec)

Records: 0  Duplicates: 0  Warnings: 0


修改字段名称

mysql> alter table emp change age age1 int(4);    #关键字change可以修改表的定义,如字段名

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field    | Type          | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename    | varchar(20)   | YES  |     | NULL    |       |

| hiredate | date          | YES  |     | NULL    |       |

| sal      | decimal(10,2) | YES  |     | NULL    |       |

| deptno   | int(2)        | YES  |     | NULL    |       |

| age1     | int(4)        | YES  |     | NULL    |       |

+----------+---------------+------+-----+---------+-------+


修改字段排列顺序

mysql> alter table emp add birth date after ename;    #新增字段birth,排在ename之后

Query OK, 0 rows affected (0.08 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field    | Type          | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename    | varchar(20)   | YES  |     | NULL    |       |

| birth    | date          | YES  |     | NULL    |       |

| hiredate | date          | YES  |     | NULL    |       |

| sal      | decimal(10,2) | YES  |     | NULL    |       |

| deptno   | int(2)        | YES  |     | NULL    |       |

| age1     | int(4)        | YES  |     | NULL    |       |

+----------+---------------+------+-----+---------+-------+

6 rows in set (0.00 sec)


把字段deptno放在age1后面

mysql> alter table emp1 modify deptno int(2) after age1;

Query OK, 0 rows affected (0.14 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp1;

+--------+-------------+------+-----+---------+-------+

| Field  | Type        | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| age1   | int(4)      | YES  |     | NULL    |       |

| deptno | int(2)      | YES  |     | NULL    |       |

| ename  | varchar(20) | YES  |     | NULL    |       |

| birth  | date        | YES  |     | NULL    |       |

+--------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)


把字段age1放在最前面

mysql> alter table emp modify age1 int(4) first;

Query OK, 0 rows affected (0.15 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field    | Type          | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| age1     | int(4)        | YES  |     | NULL    |       |

| ename    | varchar(20)   | YES  |     | NULL    |       |

| birth    | date          | YES  |     | NULL    |       |

| hiredate | date          | YES  |     | NULL    |       |

| sal      | decimal(10,2) | YES  |     | NULL    |       |

| deptno   | int(2)        | YES  |     | NULL    |       |

+----------+---------------+------+-----+---------+-------+


修改表名:

mysql> alter table emp rename emp1;

Query OK, 0 rows affected (0.02 sec)


mysql> show tables;

+---------------+

| Tables_in_zwj |

+---------------+

| emp1          |

+---------------+

1 row in set (0.00 sec)


2 DML语句示例

1)插入记录

mysql> insert into emp1(age1,ename,birth,deptno) values('555','aaa','2016-10-30','5');

Query OK, 1 row affected (0.03 sec)

也可以不指定字段名称,但values后面的顺序应该和字段的排列顺序一致

mysql> insert into emp1 values('666','bbb','2016-12-30','8');

mysql> select * from emp1;

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

|  555 | aaa   | 2016-10-30 |      5 |

|  666 | bbb   | 2016-12-30 |      8 |

+------+-------+------------+--------+

一次插入多条记录

mysql> insert into emp1(age1,ename,birth,deptno) 

    -> values ('111','ccc','2011-11-30','4'),

    -> ('666','ddd','2014-12-22','11'),

    -> ('888','eee','2015-11-30','22'),

    -> ('333','fff','2011-04-30','8');

Query OK, 4 rows affected (0.02 sec)

Records: 4  Duplicates: 0  Warnings: 0


mysql> select * from emp1;

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

|  555 | aaa   | 2016-10-30 |      5 |

|  666 | bbb   | 2016-12-30 |      8 |

|  111 | ccc   | 2011-11-30 |      4 |

|  666 | ddd   | 2014-12-22 |     11 |

|  888 | eee   | 2015-11-30 |     22 |

|  333 | fff   | 2011-04-30 |      8 |

+------+-------+------------+--------+

6 rows in set (0.00 sec)


2)更新记录,通过update命令进行更改

mysql> update emp1 set age1=1000 where ename='aaa';

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from emp1;

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

| 1000 | aaa   | 2016-10-30 |      5 |

|  666 | bbb   | 2016-12-30 |      8 |

|  111 | ccc   | 2011-11-30 |      4 |

|  666 | ddd   | 2014-12-22 |     11 |

|  888 | eee   | 2015-11-30 |     22 |

|  333 | fff   | 2011-04-30 |      8 |

+------+-------+------------+--------+

6 rows in set (0.00 sec)


3)删除记录:

mysql> delete from emp1 where ename='bbb';

Query OK, 1 row affected (0.02 sec)


mysql> select * from emp1;

+------+--------+-------+------------+

| age1 | deptno | ename | birth      |

+------+--------+-------+------------+

|  111 |      4 | ccc   | 2011-11-30 |

|  666 |     11 | ddd   | 2014-12-22 |

|  888 |     22 | eee   | 2015-11-30 |

|  333 |      8 | fff   | 2011-04-30 |

+------+--------+-------+------------+

4 rows in set (0.00 sec)


4)查询记录

mysql> select age1,ename from zwj.emp1;

+------+-------+

| age1 | ename |

+------+-------+

|  666 | bbb   |

|  111 | ccc   |

|  666 | ddd   |

|  888 | eee   |

|  333 | fff   |

+------+-------+


把表中的记录去掉重复后显示出来,

mysql> select distinct age1 from emp1;        #distinct是关键字,age1是字段名


条件查询

mysql> select * from emp1 where age1='666';

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

|  666 | bbb   | 2016-12-30 |      8 |

|  666 | ddd   | 2014-12-22 |     11 |

+------+-------+------------+--------+


组合条件查询:

mysql> select * from emp1 where ename='bbb' and birth<'2017-01-01';

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

|  666 | bbb   | 2016-12-30 |      8 |

+------+-------+------------+--------+

mysql> select * from emp1 where ename='bbb' or birth<'2017-01-30';

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

|  666 | bbb   | 2016-12-30 |      8 |

|  111 | ccc   | 2011-11-30 |      4 |

|  666 | ddd   | 2014-12-22 |     11 |

|  888 | eee   | 2015-11-30 |     22 |

|  333 | fff   | 2011-04-30 |      8 |

+------+-------+------------+--------+

5 rows in set (0.00 sec)


模式匹配:

mysql> select * from zwj.emp1 where ename regexp '^c';    #关键字regexp支持正则表达式

+------+--------+-------+------------+

| age1 | deptno | ename | birth      |

+------+--------+-------+------------+

|  111 |      4 | ccc   | 2011-11-30 |

+------+--------+-------+------------+

1 row in set (0.00 sec)

mysql> select * from zwj.emp1 where ename like 'c_c';     #短横表示匹配任意单个字符

+------+--------+-------+------------+

| age1 | deptno | ename | birth      |

+------+--------+-------+------------+

|  111 |      4 | ccc   | 2011-11-30 |

+------+--------+-------+------------+

1 row in set (0.01 sec)

mysql> select * from zwj.emp1 where ename like 'c%';     #%表示任意字符

+------+--------+-------+------------+

| age1 | deptno | ename | birth      |

+------+--------+-------+------------+

|  111 |      4 | ccc   | 2011-11-30 |

+------+--------+-------+------------+

1 row in set (0.00 sec)


排序和限制:关键字order by(默认升序排序)

mysql> select * from emp1 order by age1;

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

|  111 | ccc   | 2011-11-30 |      4 |

|  333 | fff   | 2011-04-30 |      8 |

|  666 | bbb   | 2016-12-30 |      8 |

|  666 | ddd   | 2014-12-22 |     11 |

|  888 | eee   | 2015-11-30 |     22 |

+------+-------+------------+--------+

5 rows in set (0.00 sec)

对age1相同的记录,如果把字段deptno从高到低排列,可使用如下命令,desc表示降序。

mysql> select * from emp1 order by age1,deptno desc;

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

|  111 | ccc   | 2011-11-30 |      4 |

|  333 | fff   | 2011-04-30 |      8 |

|  666 | ddd   | 2014-12-22 |     11 |

|  666 | bbb   | 2016-12-30 |      8 |

|  888 | eee   | 2015-11-30 |     22 |

+------+-------+------------+--------+

5 rows in set (0.01 sec)

对age1相同的记录,如果把字段deptno从低到高排列,可使用如下命令,asc表示升序。

mysql> select * from emp1 order by age1,deptno asc;

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

|  111 | ccc   | 2011-11-30 |      4 |

|  333 | fff   | 2011-04-30 |      8 |

|  666 | bbb   | 2016-12-30 |      8 |

|  666 | ddd   | 2014-12-22 |     11 |

|  888 | eee   | 2015-11-30 |     22 |

+------+-------+------------+--------+

5 rows in set (0.01 sec)

选择排序后的前3条记录

mysql> select * from emp1 order by age1 limit 3;

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

|  111 | ccc   | 2011-11-30 |      4 |

|  333 | fff   | 2011-04-30 |      8 |

|  666 | bbb   | 2016-12-30 |      8 |

+------+-------+------------+--------+

3 rows in set (0.00 sec)

降序排列后的前3条记录

mysql> select * from emp1 order by age1 desc limit 3;

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

|  888 | eee   | 2015-11-30 |     22 |

|  666 | bbb   | 2016-12-30 |      8 |

|  666 | ddd   | 2014-12-22 |     11 |

+------+-------+------------+--------+

3 rows in set (0.00 sec)

显示以age1字段排序后,从第2条记录开始的前4条记录

mysql> select * from emp1 order by age1 limit 1,4;

+------+-------+------------+--------+

| age1 | ename | birth      | deptno |

+------+-------+------------+--------+

|  333 | fff   | 2011-04-30 |      8 |

|  666 | bbb   | 2016-12-30 |      8 |

|  666 | ddd   | 2014-12-22 |     11 |

|  888 | eee   | 2015-11-30 |     22 |

+------+-------+------------+--------+

4 rows in set (0.00 sec)


3 DCL语句示例:

新建用户并且授权

mysql> grant select,insert on mysql.* to 'abc'@'localhost' identified by 'abc';

Query OK, 0 rows affected (0.08 sec)


撤消权限

mysql> revoke insert on mysql.* from 'abc'@'localhost';

Query OK, 0 rows affected (0.00 sec)


查看当前用户权限

mysql> show grants;

+---------------------------------------------------------------------+

| Grants for root@localhost                                           |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |

+---------------------------------------------------------------------+


查看某个用户权限

mysql> show grants for 'abc'@'localhost';

+------------------------------------------------------------------------------------------------------------+

| Grants for abc@localhost                                                                                   |

+------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'abc'@'localhost' IDENTIFIED BY PASSWORD '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' |

| GRANT SELECT ON `mysql`.* TO 'abc'@'localhost'                                                             |

+------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)


本文转自 zengwj1949 51CTO博客,原文链接:http://blog.51cto.com/zengwj1949/1920510


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysql sql_mode 汇总整理
-- ANSI 使sql符合标准sql This mode changes syntax and behavior to conform more closely to standard S...
504 0
Mysql SQL Mode详解
Mysql SQL Mode简介 MySQL服务器能够工作在不同的SQL模式下,并能针对不同的客户端以不同的方式应用这些模式。这样,应用程序就能对服务器操作进行量身定制以满足自己的需求。
1038 0
SQL server 2008 最小化安装(学习专用)
之后下载这两个安装包,根据电脑实际情况下载,这里我选择x64版本的两个轻量级安装包,其他版本比较大,学习的话这两个版本就够了
0 0
VS2017调用SQL server 2019 和C++连接语句字符串
VS2017调用SQL server 2019 和C++连接语句字符串
0 0
函数计算 Python 连接 SQL Server
函数计算 Python 连接 SQL Server 自制脑图
0 0
SQL Server 2000 函数使用---CAST 和 CONVERT
日期格式样式,借以将 datetime 或 smalldatetime 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型);或者字符串格式样式,借以将 float、real、money 或 smallmoney 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型)。
0 0
【Sql Server】进阶之行数据转为列显示
在开发系统维护阶段,经常需要进行各种数据统计,各种报表之类的。 这个时候,行数据转数据显示就发挥作用了。 场景:行数据的某列值想作为字段列显示
0 0
【Sql Server】时间转换和查询时间范围查询不正确的原因
最近在做时间方法封装的时候发现了一个问题! 如果sql语句输出的时间字段转为了字符串输出,那么在使用此字段作为时间范围筛选时发现无效了,没法过滤对应的时间范围内记录
0 0
【Sql Server】基础之统计库龄语句,仅作为语句使用
知识点的综合使用 分组、数据转换、Case when then、max、min、count、sum、left join多表关联等知识点
0 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
MySQL 5.7让优化更轻松
立即下载
PostgresChina2018_张启程_为什么我们抛弃MongoDB和MySQL,选择PgSQL
立即下载
SQL Sever迁移PG经验
立即下载