Sql基本应用

分类

DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据的查询语言

数据类型

整型 int   smallint   mediumint    bigint    tinyint（0-255）

浮点型   float  double  

字符型  char  varchar  text  longtext  mediumtext  

时间  date  datetime   time  timestamo  year

列属性

primary  key  主键约束
not  null  非空约束
unique key  唯一约束
default  默认值
auto_increment  自增长
comment  注释

字符类型

utf8
utf8mb4   支持表情

库的基本操作

创建并查看库

mysql> create database school charset utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show create database school;
+----------+-----------------------------------------------------------------+
 Database  Create Database 
+----------+-----------------------------------------------------------------+
 school  CREATE DATABASE `school` *!40100 DEFAULT CHARACTER SET utf8 * 
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

修改库

mysql> ALTER DATABASE school CHARSET utf8mb4;
Query OK, 1 row affected (0.00 sec)

删除库

mysql> drop database school;
Query OK, 0 rows affected (0.00 sec)

表的基本操作

创建

create table student(
列1  属性（数据类型、约束、其他属性） ，
列2  属性，
列3  属性
)

mysql> use school;
Database changed
mysql> CREATE TABLE student(
 -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
 -> sname VARCHAR(255) NOT NULL COMMENT '姓名',
 -> sage TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
 -> sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' ,
 -> intime TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
 -> ) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';
Query OK, 0 rows affected (0.02 sec)

修改表

加入一列QQ

mysql> ALTER TABLE student ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq号';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

在sname后面加入wechat列

mysql> ALTER TABLE student ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

在最前面加入一列数字列

mysql> ALTER TABLE student ADD num INT NOT NULL COMMENT '数字' FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除刚才添加的列

mysql> ALTER TABLE student DROP num;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE student DROP wechat;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE student DROP qq;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

修改列数据类型

mysql> ALTER TABLE student MODIFY sname VARCHAR(128) NOT NULL ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

将sgender 改为 sg 数据类型改为 CHAR 类型

mysql> ALTER TABLE student CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

表属性查询

mysql> show tables;
+------------------+
 Tables_in_school 
+------------------+
 student 
+------------------+
1 row in set (0.00 sec)

mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
 Field  Type  Null  Key  Default  Extra 
+--------+---------------------+------+-----+-------------------+----------------+
 id  int(11)  NO  PRI  NULL  auto_increment 
 sname  varchar(128)  NO   NULL  
 sage  tinyint(3) unsigned  NO   0  
 sg  char(1)  NO   n  
 intime  timestamp  NO   CURRENT_TIMESTAMP  
+--------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 Table  Create Table 
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 student  CREATE TABLE `student` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
 `sname` varchar(128) NOT NULL,
 `sage` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
 `sg` char(1) NOT NULL DEFAULT 'n',
 `intime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表' 
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除表

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