SQL分类可以分为三类:
①DDL:数据定义语言,定义库,表结构等,包括create,drop,alter等;
②DML:数据操作语言,增删改查数据,包括insert,delete,update,select等;
③DCL:数据控制语言,权限,事务等管理。(暂无)
一、DML
1.查看当前mysql服务器中有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
2、使用某个数据库
mysql> use test;
Database changed
3.查看数据库中都有什么表格?因为mysql是关系型数据库,所以库下面是表格
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| stu |
+----------------+
4、创建一个表格
mysql> create table info(`id` int,`name` varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> desc info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5、插入数据
mysql> insert into info values(1,'林大侠');
ERROR 1366 (HY000): Incorrect string value: '\xC1\xD6\xB4\xF3\xCF\xC0' for column 'name' at row 1
解决方案(在命令行窗口记住打印,才会解决编码问题):
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
再次插入数据:
mysql> insert into info values(1,'林大侠');
Query OK, 1 row affected (0.01 sec)
6.查看数据
mysql> select * from info;
+------+--------+
| id | name |
+------+--------+
| 1 | 林大侠 |
+------+--------+
1 row in set (0.00 sec)
二、DDL
1.与数据库相关的(show&use&create&drop)
①查看所有的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
②使用某一个数据库,或者指定针对哪个数据库进行操作
mysql> use hellodb;
Database changed
③创建数据库
mysql> create database hellodb;
Query OK, 1 row affected (0.00 sec)
④删除数据库
mysql> drop database hellodb;
Query OK, 0 rows affected (0.00 sec)
2.与表相关的
①查看某个数据库下的所有表格
(1)必须要求前面有use 数据库名;语句(前提)
mysql> use hellodb;
Database changed
mysql> show tables;
Empty set (0.00 sec)
(2)show tables from 数据库名;
mysql> show tables from hellodb;
Empty set (0.00 sec)
②创建表格
mysql> create table hellodb(
-> sid int,
-> sanme varchar(20),
-> age int,
-> gender char,
-> brithday date,
-> score double(4,1)
-> );
Query OK, 0 rows affected (0.01 sec)
③删除表格
drop table hellodb;
④查看表结构
mysql> desc hellodb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sid | int(11) | YES | | NULL | |
| sanme | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| brithday | date | YES | | NULL | |
| score | double(4,1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
⑤修改表结构
(1)增加一个字段
alter table 表名称 add 【column】 字段名 数据类型;
mysql> alter table hellodb add adress varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc hellodb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sid | int(11) | YES | | NULL | |
| sanme | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| brithday | date | YES | | NULL | |
| score | double(4,1) | YES | | NULL | |
| adress | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
alter table 表名称 add 【column】 字段名 数据类型 【first】
mysql> alter table hellodb add sno int first;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc hellodb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sno | int(11) | YES | | NULL | |
| sid | int(11) | YES | | NULL | |
| sanme | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| brithday | date | YES | | NULL | |
| score | double(4,1) | YES | | NULL | |
| adress | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
alter table 表名称 add 【column】 字段名 数据类型 【after 另一个字段名】
mysql> alter table hellodb add email varchar(32) after brithday;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc hellodb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sno | int(11) | YES | | NULL | |
| sid | int(11) | YES | | NULL | |
| sanme | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| brithday | date | YES | | NULL | |
| email | varchar(32) | YES | | NULL | |
| score | double(4,1) | YES | | NULL | |
| adress | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
9 rows in set (0.01 sec)
(2)删除一个字段
mysql> alter table hellodb drop adress;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc hellodb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sno | int(11) | YES | | NULL | |
| sid | int(11) | YES | | NULL | |
| sanme | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| brithday | date | YES | | NULL | |
| email | varchar(32) | YES | | NULL | |
| score | double(4,1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
(3)修改字段名
mysql> alter table hellodb change email mail varchar(22);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc hellodb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sno | int(11) | YES | | NULL | |
| sid | int(11) | YES | | NULL | |
| sanme | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| brithday | date | YES | | NULL | |
| mail | varchar(22) | YES | | NULL | |
| score | double(4,1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
(4)修改字段的数据类型,位置
alter table 表名称 modify 【column】 字段名 数据类型;
alter table 表名称 modify 【column】 字段名 数据类型 【first】;
alter table 表名称 modify 【column】 字段名 数据类型 【after 另一个字段名】;
mysql> alter table hellodb modify age char(11);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc hellodb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sno | int(11) | YES | | NULL | |
| sid | int(11) | YES | | NULL | |
| sanme | varchar(20) | YES | | NULL | |
| age | char(11) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| brithday | date | YES | | NULL | |
| mail | varchar(22) | YES | | NULL | |
| score | double(4,1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
⑥重命名表
(1)alter table 表名称 rename 新表名称;
mysql> alter table hellodb rename daxiadb;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| daxiadb |
+-------------------+
1 row in set (0.00 sec)
(2)rename table 旧表名称 to 新表名称;
mysql> rename table daxiadb to helldb;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| helldb |
+-------------------+
1 row in set (0.00 sec)
#轻松一刻:
☝上述分享来源个人总结,如果分享对您有帮忙,希望您积极转载;如果您有不同的见解,希望您积极留言,让我们一起探讨,您的鼓励将是我前进道路上一份助力,非常感谢!我会不定时更新相关技术动态,同时我也会不断完善自己,提升技术,希望与君同成长同进步!
☞本人博客:https://coding0110lin.blog.csdn.net/ 欢迎转载,一起技术交流吧!