《深入浅出MySQL》之SQL基础

语法：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-09-15 | 10000.00 |    1 |
| jeff  |   23 | 2017-09-15 | 10000.00 |    2 |
| mei   |   21 | 2017-09-15 |  8000.00 |    3 |
+-------+------+------------+----------+------+
4 rows in set (0.00 sec)
mysql> update emp set sal=‘4000‘ where name=‘
                        

                            

                                
< 上一篇
oracle pl/sql 控制结构(分支，循环，控制)
                            
                            

                                 
下一篇 >
mysql