首页 / MYSQL / MySQL之SQL基础
MySQL之SQL基础
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL之SQL基础，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含14588字，纯文字阅读大概需要21分钟。
内容图文
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 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
-> (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 |
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)
本文出自 “一万年太久，只争朝夕” 博客，请务必保留此出处http://zengwj1949.blog.51cto.com/10747365/1920510
原文：http://zengwj1949.blog.51cto.com/10747365/1920510
内容总结
以上是互联网集市为您收集整理的MySQL之SQL基础全部内容，希望文章能够帮你解决MySQL之SQL基础所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。