mysql基本语句合集--超详细

1.创建数据库

CREATE DATABASE University;

2.展示数据库

SHOW DATABASES;

3.使用数据库

USE University;

4.创建表

CREATE TABLE student(
Sno CHAR(8),
Sname CHAR(10),
Ssex CHAR(2),
Sage SMALLINT,
Major CHAR(20)
);

create table student(id int,name varchar(20));

5.创建表 并指定存储引擎

create table lyp(id int,name varchar(20)) engine=myisam;

查看存储引擎：

root@liuyq 19:46  mysql>show create table lyp;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| lyp   | CREATE TABLE `lyp` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6.查看表结构 desc

root@sc 19:04  mysql>desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

查看表结构

root@ytm 12:55  mysql>show create table city;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| city  | CREATE TABLE `city` (
  `name` varchar(20) DEFAULT NULL,
  `prov` varchar(20) DEFAULT NULL,
  `peplo` int(11) NOT NULL,
  `food` varchar(20) DEFAULT NULL,
  `happydata` int(6) DEFAULT NULL,
  PRIMARY KEY (`peplo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

7.删表

root@sc 19:07  mysql>drop table t1;

8.删库

root@sc 19:08  mysql>drop database sc;

9.插入

root@ytm 19:14  mysql>insert into student(id,name) values(1,'cali');

10.查询

root@ytm 19:14  mysql>select * from student;
+------+-------+
| id   | name  |
+------+-------+
|    1 | cali  |
|    1 | cali2 |
|    3 | cali2 |
+------+-------+

11.按条件删除

root@ytm 19:15  mysql> delete from student where id=1;
Query OK, 2 rows affected (0.00 sec)

root@ytm 19:37  mysql>select * from student;
+------+-------+
| id   | name  |
+------+-------+
|    3 | cali2 |
+------+-------+

where 指定条件
from 从哪个表

12.设置主键的两种方法
函数法

root@ytm 12:55  mysql>create  table stu2(id int,name varchar(20),gred int,primary key(id));
Query OK, 0 rows affected (0.02 sec)

root@ytm 13:01  mysql>desc stu2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| gred  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

属性法

root@ytm 13:03  mysql>create  table stu3(id int primary key,name varchar(20),gred int);
Query OK, 0 rows affected (0.01 sec)

root@ytm 13:03  mysql>desc stu2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| gred  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

13.查看存储引擎

root@ytm 13:05  mysql>show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

14.创建主键和外键

CREATE TABLE dept(
  deptid INTEGER,
  dname VARCHAR(20),
  PRIMARY KEY(deptid) 
); 
employee 员工
department 部门
REFERENCES 参考，引用

CREATE TABLE emp(