SQL是Structure Query language(結構化查詢語言)的縮寫,它是使用關係模型的資料庫應用語言。在眾多開源資料庫中,MySQL正是其中最傑出的代表,MySQL是由三個瑞典人於20世紀90年代開發的一個關係型資料庫。並用了創始人之一Michael Widenius女兒的名字My命名 ...
SQL是Structure Query language(結構化查詢語言)的縮寫,它是使用關係模型的資料庫應用語言。在眾多開源資料庫中,MySQL正是其中最傑出的代表,MySQL是由三個瑞典人於20世紀90年代開發的一個關係型資料庫。並用了創始人之一Michael Widenius女兒的名字My命名,這就是MySQL的由來,本次博客使用的是開源資料庫MySQL,版本5.7.19,下麵就開始吧!
SQL分類
1.數據定義語句(Data Definition Language,DDL):主要是用來定義資料庫、表、列等對象;
2.數據操作語句(Data Manipulation language,DML):用來添加、更新、刪除和查詢資料庫記錄,並檢查數據完整性;
3.數據控制語句(Data Control language,DCL):定義了資料庫、表、用戶的訪問許可權和安全級別等;
DDL語句
1.創建資料庫
語法:create database dbname;
舉例:
mysql> create database test; Query OK, 1 row affected (0.01 sec)
查看當前系統中有哪些資料庫:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
選擇要操作的資料庫:
mysql> use test Database changed
查看資料庫中所創建的表:
mysql> show tables; Empty set (0.00 sec) mysql> use mysql Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | dept | | emp | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 33 rows in set (0.00 sec)View Code
2.刪除資料庫
語法:drop database dbname;
舉例:
mysql> drop database test; Query OK, 0 rows affected (0.00 sec)
註意:刪除資料庫後,資料庫下麵的所有的的表就被清空了,再刪除之前記得備份有用的數據。
3.創建表
create table tablename( column_name_1 column_type_1 constraints, column_name_2 column_type_2 constraints, ... column_name_2 column_type_2 constraints, );
舉例:
mysql> create table emp(name varchar(10),hiredate date,sal decimal(10,2),dept int(2)); Query OK, 0 rows affected (0.04 sec)
查看表:
語法:desc tablename
舉例:
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | dept | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
查看表的詳細信息:
mysql> show create table emp \G; *************************** 1. row *************************** Table: emp Create Table: CREATE TABLE `emp` ( `name` varchar(10) DEFAULT NULL, `hiredate` date DEFAULT NULL, `sal` decimal(10,2) DEFAULT NULL, `dept` int(2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified
4.刪除表
語法:drop table tablename
舉例:
mysql> drop table emp; Query OK, 0 rows affected (0.01 sec)
5.修改表
(1)修改表類型:
語法:alter table tablename modify column_name column_type_new
舉例:
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | dep | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table emp modify name varchar(20); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table emp modify name varchar(20); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | dep | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)View Code
(2)增加表欄位:
語法:alter table tablename add column column_name column_type;
舉例:
mysql> alter table emp add column age int(3); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | dep | int(2) | YES | | NULL | | | age | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
(3)刪除表欄位:
alter table tablename drop column column_name
舉例:
mysql> alter table emp drop column age; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | dep | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
(4)欄位改名
語法:alter table tablename change old_column_name new_column_name column_type
舉例:
mysql> alter table emp change dep dept int(3); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | dept | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
change和midify都可以修改表,change可以修改列的名稱和重新定義列的類型,modify卻不能修改列的名稱。
(5)修改欄位排列的順序
語法:alter table tablename change/add/modify column_name column_type first/after column_name
舉例:
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | dept | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> mysql> mysql> alter table emp add column age int(2) after name; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | age | int(2) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | dept | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
(6)修改表名
語法:alter table tablename rename new_tablename
舉例:
mysql> alter table emp rename emp1; Query OK, 0 rows affected (0.01 sec) mysql> desc emp1; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | age | int(2) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | dept | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
DML語句
DML是對資料庫中的表的操作,是開發人員最長使用的。
1.插入記錄
語法:insert into tablename(column_name1,column_name2,...column_namen) values(value1,value2,...valuen);
舉例:
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | age | int(2) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | dept | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> insert into emp(name,age,hiredate,sal,dept) values('frank',22,'2017-09-15','10000',1); Query OK, 1 row affected (0.00 sec) mysql> insert into emp values('rose',21,'2017-09-15','10000',1); #也可以不指定欄位名稱,但是後面的順序必須和欄位保持一致 Query OK, 1 row affected (0.00 sec) mysql> insert into emp values('jeff',23,'2017-09-15','10000',2),('mei',21,'2017-09-15','8000',3); #可以同時插入多條記錄 Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into emp(name,sal) values('lisa','1000'); #沒寫的欄位預設為NULL Query OK, 1 row affected (0.00 sec)
查看表emp:
mysql> select * from emp; +-------+------+------------+----------+------+ | name | age | hiredate | sal | dept | +-------+------+------------+----------+------+ | frank | 22 | 2017-09-15 | 10000.00 | 1 | | rose | 21 | 2017-09-15 | 10000.00 | 1 | | jeff | 23 | 2017-09-15 | 10000.00 | 2 | | mei | 21 | 2017-09-15 | 8000.00 | 3 | | lisa | NULL | NULL | 1000.00 | NULL | +-------+------+------------+----------+------+ 5 rows in set (0.00 sec)
2.更新記錄
(1)更新單個表
語法:update tablename set column_name1=value1,column_name2=value2,...column_namen=valuen [where condition]
舉例:
mysql> select * from emp; +-------+------+------------+----------+------+ | name | age | hiredate | sal | dept | +-------+------+------------+----------+------+ | frank | 22 | 2017-09-15 | 10000.00 | 1 | | rose | 21 | 2017