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 */ | +----------+----------------------------------------------------------------------+View Code
3、查看当前系统所有库
show databases;
+--------------------+ | Database | +--------------------+ | information_schema | | info | | mysql | | performance_schema | | test | | userinfo | +--------------------+View Code
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 |
+---------------------------+
View Code
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 setView Code
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 setView Code
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 setView Code
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 setView Code
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 setView Code
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 setView Code
mysql基础操作
标签：style 字符集 图片 浮点型 play mysql基础 数据 ddr userinfo