mysql基础操作

2、创建库

create database 库名 default character set=字符集;     # 此处如果不指定，则使用系统默认字符集

mysql> create database userinfo default character set=utf8mb4;
Query OK, 1 row affected
mysql> show create database userinfo;
+----------+----------------------------------------------------------------------+
| Database | Create Database                                                      |
+----------+----------------------------------------------------------------------+
| userinfo | CREATE DATABASE `userinfo` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+----------------------------------------------------------------------+

3、查看当前系统所有库

show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| info               |
| mysql              |
| performance_schema |
| test               |
| userinfo           |
+--------------------+

4、使用库

use 库名;

5、删除库

drop database 库名;                # 此操作很危险，删除库的同时删除库下的所有表

二、数据类型

1 、数值型

‘‘‘
unsigned:表示无符号的  只针对数值型
float(M,D) 浮点型
decimal(M,D) 定点型  比float更加准确  `money` decimal(10,2) NOT NULL DEFAULT ‘0.00‘ COMMENT ‘充值金额‘,
M:精度(总位数)  D:标度(小数位)
‘‘‘

2、 字符串类型

3、 时间类型

 4、 特殊的NULL类型

‘‘‘
NULL不是假，也不是真，而是空
NULL的判断只能用is null， is not null
NULL影响查询速度，一般避免其值为NULL
‘‘‘

三、表操作

1、查看当前数据库中所有表

show tables;

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

2、创建表

create table student(
id int not null auto_increment primary key,
name varchar(10) character set utf8mb4  not null default ‘‘ comment ‘姓名‘
) default character set=‘utf8mb4‘;

# auto_increment  自增 
# default ‘‘ 默认是空
# comment ‘注释‘
# primary key 主键   一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一

3、修改表名

alter table current_table_name rename to new_table_name;

mysql> alter table student rename to accountinfo;
Query OK, 0 rows affected

mysql> show tables;
+--------------------+
| Tables_in_userinfo |
+--------------------+
| accountinfo        |
+--------------------+
1 row in set

3、清空表

3.1  delete from 表名;           # delete 清空表，如果遇到自增列，会从delete前最后一行的自增列开始计数

mysql> select * from accountinfo;
+----+-------+
| id | name  |
+----+-------+
|  1 | li    |
|  2 | fred  |
|  3 | fred2 |
|  4 | fred3 |
|  5 | fred4 |
+----+-------+
5 rows in set

mysql> delete from accountinfo;
Query OK, 5 rows affected

mysql> insert into accountinfo(name) values(‘fred4‘);
Query OK, 1 row affected

mysql> select * from accountinfo;
+----+-------+
| id | name  |
+----+-------+
|  6 | fred4 |
+----+-------+
1 row in set

3.2  truncate table 表名;         #  truncate 清空表  如果遇到自增列，会从头开始   

mysql> truncate table
 accountinfo;
Query OK, 0 rows affected

mysql> insert into accountinfo(name) values(‘fred4‘);
Query OK, 1 row affected

mysql> insert into accountinfo(name) values(‘fred4‘);
Query OK, 1 row affected

mysql> select * from accountinfo;
+----+-------+
| id | name  |
+----+-------+
|  1 | fred4 |
|  2 | fred4 |
+----+-------+
2 rows in set

4、删除表

drop table 表名;

5、修改表

5.1 添加字段

alter table student add 字段名 tinyint(4) not null default ‘0‘ comment ‘地址‘;      # 默认是add到表的最后一列

alter table student add phone int(11) not null default ‘0‘ comment ‘电话‘ after `name`;               # 在指定字段后面添加

mysql> alter table student add addr tinyint(4) not null default ‘0‘ comment ‘地址
‘;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     |         |                |
| sex   | tinyint(4)  | NO   |     | 0       |                |
| addr  | tinyint(4)  | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set

mysql> alter table student add phone int(11) not null default ‘0‘ comment ‘电话‘ after `name`;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     |         |                |
| phone | int(11)     | NO   |     | 0       |                |
| sex   | tinyint(4)  | NO   |     | 0       |                |
| addr  | tinyint(4)  | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set

5.2 修改字段

alter table student modify addr varchar(32) not null default ‘‘ comment ‘地址‘;

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     |         |                |
| phone | int(11)     | NO   |     | 0       |                |
| sex   | tinyint(4)  | NO   |     | 0       |                |
| addr  | tinyint(4)  | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+

mysql> alter table student modify addr varchar(32) not null default ‘‘ comment ‘地址‘;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     |         |                |
| phone | int(11)     | NO   |     | 0       |                |
| sex   | tinyint(4)  | NO   |     | 0       |                |
| addr  | varchar(32) | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set

5.3 删除字段

alter table student drop addr; 

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     |         |                |
| phone | int(11)     | NO   |     | 0       |                |
| sex   | tinyint(4)  | NO   |     | 0       |                |
| addr  | varchar(32) | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set

mysql> alter table student drop addr;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     |         |                |
| phone | int(11)     | NO   |     | 0       |                |
| sex   | tinyint(4)  | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set

mysql基础操作

标签：style   字符集   图片   浮点型   play   mysql基础   数据   ddr   userinfo