MySQL学习(一)：建库、建表、插入、修改、删除

建立数据库

  mysql> create database test1;  
  Query OK, 1 row affected (0.02 sec)  

建立了数据再建立，则：

  mysql> create database test1;  
  ERROR 1007 (HY000): Can not create database 'test1'; database exists

显示数据库信息：

  mysql> show databases;  
  +--------------------+  
  | Database           |  
  +--------------------+  
  | information_schema |  
  | mysql              |  
  | performance_schema |  
  | test               |  
  | test1              |  
  +--------------------+  
  5 rows in set (0.05 sec)  

进入数据库

  mysql> use test1;  
  Database changed

显示表中的数据库：

  mysql> show tables;  
  Empty set (0.00 sec)  

删除数据库：

  mysql> drop database test1;  
  Query OK, 0 rows affected (0.07 sec)

建立表：

  mysql> create database test1;  
  Query OK, 1 row affected (0.01 sec)  
  mysql> use test1;  
  Database changed  
  mysql> create table emp(ename varchar(10), hiredate date , sal decimal(10,2), deptno int(2));  
  Query OK, 0 rows affected (0.05 sec)  
  mysql> desc 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    |       |  
  +----------+---------------+------+-----+---------+-------+  
  4 rows in set (0.00 sec)

显示DDL语句：

  mysql> show create table emp \G;  
  *************************** 1.row ***************************  
  Table: emp  
  Create Table: 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  
  1 row in set (0.00 sec)
  ERROR:   
  No query specified  

删除表：

  mysql> drop table emp;  
  Query OK, 0 rows affected (0.01 sec)  

修改表字段名称，删除表字段

  mysql> create table emp(ename varchar(10), hiredate date , sal decimal(10,2), deptno int(2));  
  Query OK, 0 rows affected (0.03 sec)  
.
  mysql> desc 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    |       |  
  +----------+---------------+------+-----+---------+-------+  
  4 rows in set (0.00 sec)  
.
  mysql> alter table emp modify ename varchar(20);  
  Query OK, 0 rows affected (0.05 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    |       |  
  +----------+---------------+------+-----+---------+-------+  
  4 rows in set (0.00 sec)  
.
  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 |  
  +----------+---------------+------+-----+---------+-------+  
  | 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 column age;  
  Query OK, 0 rows affected (0.06 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    |       |  
  +----------+---------------+------+-----+---------+-------+  
  3 rows in set (0.00 sec)  
.
  mysql> alter table emp change age age1 int(4);  
  ERROR 1054 (42S22): Unknown column 'age' in 'emp'  
  mysql> alter table emp change sal sal_salart int(4);  
  Query OK, 0 rows affected (0.04 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_salart | int(4)      | YES  |     | NULL    |       |  
  +------------+-------------+------+-----+---------+-------+  
  3 rows in set (0.00 sec)  

修改表字段顺序：

  mysql> alter table emp add birth date after ename;  
  Query OK, 0 rows affected (0.05 sec)  
  Records: 0  Duplicates: 0  Warnings: 0  
.
  mysql> alter table emp add testColumn date after 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    |       |  
  | testColumn | date        | YES  |     | NULL    |       |  
  | birth      | date        | YES  |     | NULL    |       |  
  | hiredate   | date        | YES  |     | NULL    |       |  
  | sal_salart | int(4)      | YES  |     | NULL    |       |  
  +------------+-------------+------+-----+---------+-------+  
  5 rows in set (0.00 sec)  
.
  mysql> alter table emp modify sal_salart int(5) first;  
  Query OK, 0 rows affected (0.06 sec)  
  Records: 0  Duplicates: 0  Warnings: 0  
.
  mysql> desc emp;  
  +------------+-------------+------+-----+---------+-------+  
  | Field      | Type        | Null | Key | Default | Extra |  
  +------------+-------------+------+-----+---------+-------+  
  | sal_salart | int(5)      | YES  |     | NULL    |       |  
  | ename      | varchar(20) | YES  |     | NULL    |       |  
  | testColumn | date        | YES  |     | NULL    |       |  
  | birth      | date        | YES  |     | NULL    |       |  
  | hiredate   | date        | YES  |     | NULL    |       |  
  +------------+-------------+------+-----+---------+-------+  
  5 rows in set (0.00 sec)  
.
  mysql> alter table emp rename emp_test;  
  Query OK, 0 rows affected (0.01 sec)  

增加字段：

  mysql> alter table emp_test add column age int(3);  
  Query OK, 0 rows affected (0.05 sec)  
  Records: 0  Duplicates: 0  Warnings: 0  
.
  mysql> desc emp_test;  
  +------------+-------------+------+-----+---------+-------+  
  | Field      | Type        | Null | Key | Default | Extra |  
  +------------+-------------+------+-----+---------+-------+  
  | sal_salart | int(5)      | YES  |     | NULL    |       |  
  | ename      | varchar(20) | YES  |     | NULL    |       |  
  | testColumn | date        | YES  |     | NULL    |       |  
  | birth      | date        | YES  |     | NULL    |       |  
  | hiredate   | date        | YES  |     | NULL    |       |  
  | age        | int(3)      | YES  |     | NULL    |       |  
  +------------+-------------+------+-----+---------+-------+  
  6 rows in set (0.00 sec)  
  mysql> alter table emp_test rename test;  
  Query OK, 0 rows affected (0.01 sec)  

插入数据：

  mysql> use test1;  
  Reading table information for completion of table and column names  
  You can turn off this feature to get a quicker startup with -A  
.
  Database changed  
.
  mysql> insert into test(ename,age) values('abc',3);  
  Query OK, 1 row affected (0.01 sec)  
.
  mysql> select * from test;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | abc   | NULL       | NULL  | NULL     |    3 |  
  +------------+-------+------------+-------+----------+------+  
  1 row in set (0.00 sec)  
.
  mysql> insert into test(ename,age) values('abc',3),('def',9);  
  Query OK, 2 rows affected (0.01 sec)  
  Records: 2  Duplicates: 0  Warnings: 0  
.
  mysql> select * from test;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | abc   | NULL       | NULL  | NULL     |    3 |  
  |       NULL | abc   | NULL       | NULL  | NULL     |    3 |  
  |       NULL | def   | NULL       | NULL  | NULL     |    9 |  
  +------------+-------+------------+-------+----------+------+  
  3 rows in set (0.00 sec)  

修改数据：

  mysql> update test set age = 8 where ename = 'def';  
  Query OK, 1 row affected (0.01 sec)  
  Rows matched: 1  Changed: 1  Warnings: 0  
.
  mysql> select * from test;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | abc   | NULL       | NULL  | NULL     |    3 |  
  |       NULL | abc   | NULL       | NULL  | NULL     |    3 |  
  |       NULL | def   | NULL       | NULL  | NULL     |    8 |  
  +------------+-------+------------+-------+----------+------+  
  3 rows in set (0.00 sec)  
.
  mysql> update test set age = 8 where ename = 'def22222222';  
  Query OK, 0 rows affected (0.01 sec)  
  Rows matched: 0  Changed: 0  Warnings: 0  
.
  mysql> select * from test;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | abc   | NULL       | NULL  | NULL     |    3 |  
  |       NULL | abc   | NULL       | NULL  | NULL     |    3 |  
  |       NULL | def   | NULL       | NULL  | NULL     |    8 |  
  +------------+-------+------------+-------+----------+------+  
  3 rows in set (0.00 sec)  
.
  mysql> update test set age = 81 where ename = 'def22222222';  
  Query OK, 0 rows affected (0.01 sec)  
  Rows matched: 0  Changed: 0  Warnings: 0  
.
  mysql> select * from test;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | abc   | NULL       | NULL  | NULL     |    3 |  
  |       NULL | abc   | NULL       | NULL  | NULL     |    3 |  
  |       NULL | def   | NULL       | NULL  | NULL     |    8 |  
  +------------+-------+------------+-------+----------+------+  
  3 rows in set (0.00 sec)  
.
  mysql> update test set age = 81 where ename = 'def';  
  Query OK, 1 row affected (0.00 sec)  
  Rows matched: 1  Changed: 1  Warnings: 0  
.
  mysql> select * from test;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | abc   | NULL       | NULL  | NULL     |    3 |  
  |       NULL | abc   | NULL       | NULL  | NULL     |    3 |  
  |       NULL | def   | NULL       | NULL  | NULL     |   81 |  
  +------------+-------+------------+-------+----------+------+  
  3 rows in set (0.00 sec)  

删除数据

  mysql> delete from test where age = 3;  
  Query OK, 2 rows affected (0.00 sec)  
.
  mysql> select * from test;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | def   | NULL       | NULL  | NULL     |   81 |  
  +------------+-------+------------+-------+----------+------+  
  1 row in set (0.00 sec)  
.
  mysql> insert test(ename,age) values ('aaaaa',3),('aaaaa',5),('ccccc',3), ('aaaaa',9), ('ttttt',2);  
  Query OK, 5 rows affected (0.01 sec)  
  Records: 5  Duplicates: 0  Warnings: 0  
.
  mysql> select * from test;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | def   | NULL       | NULL  | NULL     |   81 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |  
  |       NULL | ccccc | NULL       | NULL  | NULL     |    3 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |  
  |       NULL | ttttt | NULL       | NULL  | NULL     |    2 |  
  +------------+-------+------------+-------+----------+------+  
  6 rows in set (0.00 sec)  

查询数据：

  mysql> alter table test rename test_col;  
  Query OK, 0 rows affected (0.01 sec)  
.
  mysql> select * from test_col;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | def   | NULL       | NULL  | NULL     |   81 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |  
  |       NULL | ccccc | NULL       | NULL  | NULL     |    3 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |  
  |       NULL | ttttt | NULL       | NULL  | NULL     |    2 |  
  +------------+-------+------------+-------+----------+------+  
  6 rows in set (0.00 sec)  
.
  mysql> alter table test_col rename test;  
  Query OK, 0 rows affected (0.01 sec)  
.
  mysql> select * from test;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | def   | NULL       | NULL  | NULL     |   81 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |  
  |       NULL | ccccc | NULL       | NULL  | NULL     |    3 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |  
  |       NULL | ttttt | NULL       | NULL  | NULL     |    2 |  
  +------------+-------+------------+-------+----------+------+  
  6 rows in set (0.00 sec)  
.
  mysql> selecdt distinct ename from test;  
  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selecdt distinct ename from test' at line 1  
  mysql> select distinct ename from test;  
  +-------+  
  | ename |  
  +-------+  
  | def   |  
  | aaaaa |  
  | ccccc |  
  | ttttt |  
  +-------+  
  4 rows in set (0.00 sec)  
.
  mysql> select * from test where ename = 'aaaaa';  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |  
  +------------+-------+------------+-------+----------+------+  
  3 rows in set (0.00 sec)  
.
  mysql> select * from test where age < 8;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |  
  |       NULL | ccccc | NULL       | NULL  | NULL     |    3 |  
  |       NULL | ttttt | NULL       | NULL  | NULL     |    2 |  
  +------------+-------+------------+-------+----------+------+  
  4 rows in set (0.00 sec)  
.
  mysql> select * from test order by age;  
  +------------+-------+------------+-------+----------+------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  |  
  +------------+-------+------------+-------+----------+------+  
  |       NULL | ttttt | NULL       | NULL  | NULL     |    2 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |  
  |       NULL | ccccc | NULL       | NULL  | NULL     |    3 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |  
  |       NULL | def   | NULL       | NULL  | NULL     |   81 |  
  +------------+-------+------------+-------+----------+------+  
  6 rows in set (0.00 sec)  
.
  mysql> alter table test add column salart int (11);  
  Query OK, 6 rows affected (0.06 sec)  
  Records: 6  Duplicates: 0  Warnings: 0  
.
  mysql> alter table test change salart salary int (11);  
  Query OK, 6 rows affected (0.05 sec)  
  Records: 6  Duplicates: 0  Warnings: 0  
.
  mysql> select * from test;  
  +------------+-------+------------+-------+----------+------+--------+  
  | sal_salart | ename | testColumn | birth | hiredate | age  | salary |  
  +------------+-------+------------+-------+----------+------+--------+  
  |       NULL | def   | NULL       | NULL  | NULL     |   81 |   NULL |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    3 |   NULL |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    5 |   NULL |  
  |       NULL | ccccc | NULL       | NULL  | NULL     |    3 |   NULL |  
  |       NULL | aaaaa | NULL       | NULL  | NULL     |    9 |   NULL |  
  |       NULL | ttttt | NULL       | NULL  | NULL     |    2 |   NULL |  
  +------------+-------+------------+-------+----------+------+--------+  
  6 rows in set (0.00 sec)  
.
  mysql> insert into test(ename,age,salary) values  ('newNane1',1,100),  
  -> ('newName2',3,80),  
  -> ('newName3',5,10);  
  Query OK, 3 rows affected (0.01 sec)  
  Records: 3  Duplicates: 0  Warnings: 0  
.
  mysql> select * from test;  
  +------------+----------+------------+-------+----------+------+--------+  
  | sal_salart | ename    | testColumn | birth | hiredate | age  | salary |  
  +------------+----------+------------+-------+----------+------+--------+  
  |       NULL | def      | NULL       | NULL  | NULL     |   81 |   NULL |  
  |       NULL | aaaaa    | NULL       | NULL  | NULL     |    3 |   NULL |  
  |       NULL | aaaaa    | NULL       | NULL  | NULL     |    5 |   NULL |  
  |       NULL | ccccc    | NULL       | NULL  | NULL     |    3 |   NULL |  
  |       NULL | aaaaa    | NULL       | NULL  | NULL     |    9 |   NULL |  
  |       NULL | ttttt    | NULL       | NULL  | NULL     |    2 |   NULL |  
  |       NULL | newNane1 | NULL       | NULL  | NULL     |    1 |    100 |  
  |       NULL | newName2 | NULL       | NULL  | NULL     |    3 |     80 |  
  |       NULL | newName3 | NULL       | NULL  | NULL     |    5 |     10 |  
  +------------+----------+------------+-------+----------+------+--------+  
  9 rows in set (0.00 sec)  
.
  mysql> select * from test order by salary;  
  +------------+----------+------------+-------+----------+------+--------+  
  | sal_salart | ename    | testColumn | birth | hiredate | age  | salary |  
  +------------+----------+------------+-------+----------+------+--------+  
  |       NULL | def      | NULL       | NULL  | NULL     |   81 |   NULL |  
  |       NULL | aaaaa    | NULL       | NULL  | NULL     |    3 |   NULL |  
  |       NULL | aaaaa    | NULL       | NULL  | NULL     |    5 |   NULL |  
  |       NULL | ccccc    | NULL       | NULL  | NULL     |    3 |   NULL |  
  |       NULL | aaaaa    | NULL       | NULL  | NULL     |    9 |   NULL |  
  |       NULL | ttttt    | NULL       | NULL  | NULL     |    2 |   NULL |  
  |       NULL | newName3 | NULL       | NULL  | NULL     |    5 |     10 |  
  |       NULL | newName2 | NULL       | NULL  | NULL     |    3 |     80 |  
  |       NULL | newNane1 | NULL       | NULL  | NULL     |    1 |    100 |  
  +------------+----------+------------+-------+----------+------+--------+  
  9 rows in set (0.00 sec)  
.
  mysql> select * from test order by salary limit 6,3;  
  +------------+----------+------------+-------+----------+------+--------+  
  | sal_salart | ename    | testColumn | birth | hiredate | age  | salary |  
  +------------+----------+------------+-------+----------+------+--------+  
  |       NULL | newName3 | NULL       | NULL  | NULL     |    5 |     10 |  
  |       NULL | newName2 | NULL       | NULL  | NULL     |    3 |     80 |  
  |       NULL | newNane1 | NULL       | NULL  | NULL     |    1 |    100 |  
  +------------+----------+------------+-------+----------+------+--------+  
  3 rows in set (0.00 sec)  

赏

支付宝扫码打赏

微信打赏

若你觉得我的文章对你有帮助,欢迎点击上方按钮对我打赏

扫描二维码,分享此文章

MySQL学习(一)：建库、建表、插入、修改、删除

CATALOG

FEATURED TAGS