MENU

sql语句简单总结—上

• March 9, 2019 • Read: 74 • python

本文总结一些常用的数据库操作和sql语句。

SQL语句主要分为:
DQL:数据查询语言,用于对数据进行查询,如select
DML:数据操作语言,对数据进行增加、修改、删除,如insert、udpate、delete
TPL:事务处理语言,对事务进行处理,包括begin transaction、commit、rollback
DCL:数据控制语言,进行授权与权限回收,如grant、revoke
DDL:数据定义语言,进行数据库、表的管理等,如create、drop
CCL:指针控制语言,通过控制指针完成表的操作,如declare cursor
重点是数据的crud(增删改查),必须熟练编写DQL、DML,能够编写DDL完成数据库、表的操作,其它语言如TPL、DCL、CCL了解即可。本文仅对DQL和DML简单总结。

数据类型

可以通过查看帮助文档查阅所有支持的数据类型
使用数据类型的原则是:够用就行,尽量使用取值范围小的,而不用大的,这样可以更多的节省存储空间

常用数据类型如下:
整数:int,bit
小数:decimal
字符串:varchar,char
日期时间: date, time, datetime
枚举类型(enum)

特别说明的类型如下:
decimal表示浮点数,如decimal(5,2)表示共存5位数,小数占2位
char表示固定长度的字符串,如char(3),如果填充'ab'时会补一个空格为'ab '
varchar表示可变长度的字符串,如varchar(3),填充'ab'时就会存储'ab'
字符串text表示存储大文本,当字符大于4000时推荐使用
对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径
更全的数据类型可以参考http://blog.csdn.net/anxpp/article/details/51284106

约束

主键primary key:物理上存储的顺序
非空not null:此字段不允许填写空值
惟一unique:此字段的值不允许重复
默认default:当不填写此值时会使用默认值,如果填写时以填写为准
外键foreign key:对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常
说明:虽然外键约束可以保证数据的有效性,但是在进行数据的crud(增加、修改、删除、查询)时,都会降低数据库的性能,所以不推荐使用,那么数据的有效性怎么保证呢?答:可以在逻辑层进行控制

数值类型(常用)

类型 字节大小 有符号范围(Signed) 无符号范围(Unsigned)
TINYINT 1 -128 ~ 127 0 ~ 255
SMALLINT 2 -32768 ~ 32767 0 ~ 65535
MEDIUMINT 3 -8388608 ~ 8388607 0 ~ 16777215
INT/INTEGER 4 -2147483648 ~2147483647 0 ~ 4294967295
BIGINT 8 -9223372036854775808 ~ 9223372036854775807 0 ~ 18446744073709551615

字符串

类型 字节大小 示例
CHAR 0-255 类型:char(3) 输入 'ab', 实际存储为'ab ', 输入'abcd' 实际存储为 'abc'
VARCHAR 0-255 类型:varchar(3) 输 'ab',实际存储为'ab', 输入'abcd',实际存储为'abc'
TEXT 0-65535 大文本

日期时间类型

类型 字节大小 示例
DATE 4 '2020-01-01'
TIME 3 '12:29:59'
DATETIME 8 '2020-01-01 12:29:59'
YEAR 1 '2017'
TIMESTAMP 4 '1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC


数据库操作

显示数据库版本:

select version();

例如:select version();

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.25-0ubuntu0.16.04.2 |
+-------------------------+
1 row in set (0.00 sec)

显示时间:

select now();

例如:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-03-09 21:35:23 |
+---------------------+
1 row in set (0.00 sec)

查看所有数据库:

show databases;

例如:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learn_sql          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

使用数据库:

use 数据库名;

例如:use learn_sql;

mysql> use learn_sql;
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

查看当前使用的数据库:

例如: select database();

mysql> select database();
+------------+
| database() |
+------------+
| learn_sql  |
+------------+
1 row in set (0.00 sec)
 

创建数据库:

create database 数据库名 charset=utf8;

例如:create database python charset=utf8;

mysql> create database python charset=utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learn_sql          |
| mysql              |
| performance_schema |
| python             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

查看创建数据库的语句:

show create database 数据库名;

例如:show create database python;

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

删除数据库:

drop database 数据库名;

例如:drop database python;

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learn_sql          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

数据表操作

查看当前数据库中的所有表:

show tables;

例如:

mysql> show tables;
+---------------------+
| Tables_in_learn_sql |
+---------------------+
| areas               |
| classes             |
| students            |
+---------------------+
3 rows in set (0.00 sec)

查看表结构:

desc 表名;

例如:desc students;

mysql> desc students;
+-----------+-------------------------------------+------+-----+---------+------                                        ----------+
| Field     | Type                                | Null | Key | Default | Extra                                                  |
+-----------+-------------------------------------+------+-----+---------+------                                        ----------+
| id        | int(10) unsigned                    | NO   | PRI | NULL    | auto_                                        increment |
| name      | varchar(20)                         | YES  |     |         |                                                        |
| age       | tinyint(3) unsigned                 | YES  |     | 0       |                                                        |
| height    | decimal(5,2)                        | YES  |     | NULL    |                                                        |
| gender    | enum('男','女','中性','保密')       | YES  |     | 保密    |                                                        |
| cls_id    | int(10) unsigned                    | YES  |     | 0       |                                                        |
| is_delete | bit(1)                              | YES  |     | b'0'    |                                                        |
+-----------+-------------------------------------+------+-----+---------+------                                        ----------+
7 rows in set (0.00 sec)

创建表:

约束:

auto_increment表示自动增长
not null 表示不能为空
primary key 表示主键
default 默认值
create table 数据表名字 (字段 类型 约束, 字段 类型 约束);

例如:

创建一个学生表,有id(主键),name,age,height,gender,clas_id这几个属性。其中,unsigned代表无符号数,就是只有正数。数据类型见上面给出来的表。

create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','人妖','保密'),
    cls_id int unsigned default 0
);

例如:

mysql> create table students(
    ->     id int unsigned primary key auto_increment not null,
    ->     name varchar(20) default '',
    ->     age tinyint unsigned default 0,
    ->     height decimal(5,2),
    ->     gender enum('男','女','人妖','保密'),
    ->     cls_id int unsigned default 0
    -> );
Query OK, 0 rows affected (0.02 sec)

查看表的创建语句:

show create table 表名;

例如:show create table students;

mysql> show create table students;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                               |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT '',
  `age` tinyint(3) unsigned DEFAULT '0',
  `height` decimal(5,2) DEFAULT NULL,
  `gender` enum('男','女','人妖','保密') DEFAULT NULL,
  `cls_id` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8       |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改表(添加字段):

alter table 表名 add 列名 数据类型;

例如:alter table students add birthday datetime;

mysql> alter table students add birthday datetime;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc students;
+----------+-------------------------------------+------+-----+---------+----------------+
| Field    | Type                                | Null | Key | Default | Extra          |
+----------+-------------------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned                    | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)                         | YES  |     |         |                |
| age      | tinyint(3) unsigned                 | YES  |     | 0       |                |
| height   | decimal(5,2)                        | YES  |     | NULL    |                |
| gender   | enum('男','女','人妖','保密')       | YES  |     | NULL    |                |
| cls_id   | int(10) unsigned                    | YES  |     | 0       |                |
| birthday | datetime                            | YES  |     | NULL    |                |
+----------+-------------------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

修改表(修改字段)——不重命名版

alter table 表名 modify 列名 类型和约束;

例如:alter table students modify birthday date not null;

mysql> alter table students modify birthday date not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+----------+-------------------------------------+------+-----+---------+----------------+
| Field    | Type                                | Null | Key | Default | Extra          |
+----------+-------------------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned                    | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)                         | YES  |     |         |                |
| age      | tinyint(3) unsigned                 | YES  |     | 0       |                |
| height   | decimal(5,2)                        | YES  |     | NULL    |                |
| gender   | enum('男','女','人妖','保密')       | YES  |     | NULL    |                |
| cls_id   | int(10) unsigned                    | YES  |     | 0       |                |
| birthday | date                                | NO   |     | NULL    |                |
+----------+-------------------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

修养表(修改字段)——重命名版:

alter table 表名 change 原名 新名 类型及约束;

例如: alter table students change birthday birth date default '2000-1-1';

mysql> alter table students change birthday birth date default '2000-1-1';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+--------+-------------------------------------+------+-----+------------+----------------+
| Field  | Type                                | Null | Key | Default    | Extra          |
+--------+-------------------------------------+------+-----+------------+----------------+
| id     | int(10) unsigned                    | NO   | PRI | NULL       | auto_increment |
| name   | varchar(20)                         | YES  |     |            |                |
| age    | tinyint(3) unsigned                 | YES  |     | 0          |                |
| height | decimal(5,2)                        | YES  |     | NULL       |                |
| gender | enum('男','女','人妖','保密')       | YES  |     | NULL       |                |
| cls_id | int(10) unsigned                    | YES  |     | 0          |                |
| birth  | date                                | YES  |     | 2000-01-01 |                |
+--------+-------------------------------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)

修改表(删除字段):

alter table 表名 drop 字段;

例如:alter table students drop birth;

mysql> alter table students drop birth;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+--------+-------------------------------------+------+-----+---------+----------------+
| Field  | Type                                | Null | Key | Default | Extra          |
+--------+-------------------------------------+------+-----+---------+----------------+
| id     | int(10) unsigned                    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)                         | YES  |     |         |                |
| age    | tinyint(3) unsigned                 | YES  |     | 0       |                |
| height | decimal(5,2)                        | YES  |     | NULL    |                |
| gender | enum('男','女','人妖','保密')       | YES  |     | NULL    |                |
| cls_id | int(10) unsigned                    | YES  |     | 0       |                |
+--------+-------------------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

删除表:

drop table 表名;

例如:drop table areas;

mysql> drop table areas
    -> ;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------------+
| Tables_in_learn_sql |
+---------------------+
| classes             |
| students            |
+---------------------+
2 rows in set (0.00 sec)

篇幅有限,数据表的增删改查在下一篇中总结。

Tags: python
Archives QR Code Tip
QR Code for this page
Tipping QR Code