溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
SQL是Structure Query Language(結(jié)構(gòu)化查詢語言）的縮寫，它是關(guān)系型數(shù)據(jù)庫的應(yīng)用語言，由IBM在20世紀(jì)70年×××發(fā)，以實(shí)現(xiàn)關(guān)系型數(shù)據(jù)庫中的信息檢索。
在20世紀(jì)80年代初，美國國家標(biāo)準(zhǔn)局（ANSI)開始著手制定SQL標(biāo)準(zhǔn)，最早的ANSI標(biāo)準(zhǔn)于1986年完成，就被叫做SQL-86。正是由于SQL語言的標(biāo)準(zhǔn)化，所以大多數(shù)關(guān)系型數(shù)據(jù)庫都支持SQL語言，它已經(jīng)發(fā)展成為多種平臺(tái)進(jìn)行交互操作的底層會(huì)話語言。
SQL的分類：
DDL：數(shù)據(jù)定義語言，即是對(duì)數(shù)據(jù)庫內(nèi)部對(duì)象進(jìn)行創(chuàng)建、刪除、修改等操作的語言，和DML最大區(qū)別在于DML僅對(duì)表內(nèi)數(shù)據(jù)進(jìn)行操作，而不涉及到表的定義、結(jié)構(gòu)的修改，更不會(huì)涉及其它對(duì)象，DBA使用較多。常用關(guān)鍵字包括create、drop、alter等。
DML：數(shù)據(jù)操作語言，用于添加、刪除、更新和查詢表中的記錄，并檢查數(shù)據(jù)的完整性，開發(fā)人員使用較多。常用的語句包括insert、delete、update、和select等。
DCL：數(shù)據(jù)控制語言，用于管理系統(tǒng)中的對(duì)象權(quán)限時(shí)使用，常用語句有g(shù)rant、revoke等。
1 DDL語句示例：
1）創(chuàng)建數(shù)據(jù)庫
mysql> show engines; #查看支持的引擎，包括默認(rèn)的引擎
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| 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; #查看系統(tǒng)支持的字符集
mysql> show variables like "character%"; #查看當(dāng)前字符集設(shè)置
mysql> show variables like "collation%"; #查看字符集校驗(yàn)設(shè)置
mysql> create database test1; #創(chuàng)建test1數(shù)據(jù)庫
Query OK, 1 row affected (0.05 sec)
mysql> show databases; #查看數(shù)據(jù)庫
+--------------------+
| 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) #創(chuàng)建時(shí)，也可以指定字符集
2）刪除數(shù)據(jù)庫
mysql> drop database test1;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
備注：數(shù)據(jù)庫一旦被刪除，庫中所有的表也將被刪除，因此，備份非常重要
3）創(chuàng)建表
mysql> use zwj; #選擇數(shù)據(jù)庫
mysql> create table emp #varchar(n)其中n代表字符數(shù)
-> (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表的結(jié)構(gòu)
+----------+---------------+------+-----+---------+-------+
| 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 #查看創(chuàng)建表的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 |
復(fù)制一張表
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
查看表的狀態(tài)，了解兩張表是否一致
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)
查看指定表的狀態(tài)信息
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); #關(guān)鍵字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); #默認(rèn)排在最后
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)
刪除一個(gè)字段
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); #關(guān)鍵字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后面的順序應(yīng)該和字段的排列順序一致
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命令進(jìn)行更改
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 |
+------+-------+
把表中的記錄去掉重復(fù)后顯示出來，
mysql> select distinct age1 from emp1; #distinct是關(guān)鍵字，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'; #關(guān)鍵字regexp支持正則表達(dá)式
+------+--------+-------+------------+
| 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'; #短橫表示匹配任意單個(gè)字符
+------+--------+-------+------------+
| 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)
排序和限制：關(guān)鍵字order by（默認(rèn)升序排序）
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)
對(duì)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)
對(duì)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語句示例：
新建用戶并且授權(quán)
mysql> grant select,insert on mysql.* to 'abc'@'localhost' identified by 'abc';
Query OK, 0 rows affected (0.08 sec)
撤消權(quán)限
mysql> revoke insert on mysql.* from 'abc'@'localhost';
Query OK, 0 rows affected (0.00 sec)
查看當(dāng)前用戶權(quán)限
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 |
+---------------------------------------------------------------------+
查看某個(gè)用戶權(quán)限
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)
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
