Mysql基本语句
查看当前所在库:select database();
切换目录: use db1;
查看:
1.查看库:show databases;
2.查看表:show tables; 查看当前库下面所有的表(文件)
show create table t12; 指定查看某个表的描述信息
desc t13; 查看表的具体信息
3.查看记录:select name,id from t13; 查看单个记录的信息
select * from t13; 查看所有记录信息
增加:
1.增加库:create database db2; create database db2 charset utf8; (增加库:1.库名 2.指定字符编号,子文件会继承)
2.增加表(即增加表头信息):create table t1(id int,name char);
alter table t1 add age int; 可以新增一个字段列,如:新增age一栏
3.增加记录:insert into t1(id,name) values(1,'zhangsan'),(2,'lisi');
<#insert into t1(name) values('wangwu'),('aaaa'),('bbbb'); 也可以单独增加某一个字段 >
更改:
1.更改库(更改字符编码):alter table db2 charset gbk;
2.更改表:
2.1alter table t1 modify name char(15); 单独更改字符类型指定的长度即存储的宽度
2.2alter table t1 change name Name char(20); 更改字段的名字
2.3alter table t1 add age int; 可以新增一个字段列 例如:新增age一栏
3.更改记录: update t1 set Name="lisi_new" where id=2;
< update db1.t1 set Name="lisi_new" where id=1;可以指定文件针对修改>
删除:(注意删除操作在公司里面慎用)
1.删除库:drop database db1;
2.删除表:drop table t1;(这是把表格文件删掉)
3.删除记录: delete from t1 where id=3; 删除某个字段,指定符合条件的
delete from t1(这是清空表里面的内容)
truncate t17; (清空表格里面的内容,而且还会清空id的累积计数)
重点:
对一个新表开始可以统一对表格字符段进行限制:
1.限定ID不能为空且唯一存在: create table t17(id int primary key auto_increment);
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set
delete与truncate的区别
delete from t1删除表格里面的内容后,数据是清理掉了但是AUTO_INCREMENT=5自动计数是会保留的,
-----------------------------------------+
| t17 | CREATE TABLE `t17` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(15) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------+
清理后再去新增name,会发下ID会从原来5开始累积的,不会从0开始:
insert into t17(name) values('aaa'),('bbb'),('ccc');
+----+------+
| id | name |
+----+------+
| 5 | aaa |
| 6 | bbb |
| 7 | ccc |
+----+------+
truncate t17;这种删除不仅清理数据,而且会清理id的自动计数。
数据库建立及操作的一些注意事项
库(文件夹)详细操作:
1.数据库区分大小写
2.不能使用关键字
格式:
create table 库名.表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
约束条件:是在数据类型之外对字段附加的额外的限制
注意:
1、最后一个字段之后不能加逗号
2. 在同一张表中,字段名是不能相同
3. 宽度和约束条件可选,字段名和类型是必须的
例如:
create table t4 ( id int, age int, sex char);
库名.表名(字段名 类型 [(宽度,约束条件)] ) 注意:一般情况下中括号里面的为可选项目
整数类型:
1.tinyint:默认是有符号的(-128,127)
show create table t5;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t5 | CREATE TABLE `t5` (
`x` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
insert into t5 values(-1); #可以输入验证是有符号的
正常情况
insert into t5 values(-127);
+------+
| x |
+------+
| -1 |
| -127 |
+------+
异常情况:(非严格模式如果输入的超出范围,会按照最接近的极限值填充)
mysql> insert into t5 values(-129);
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql> select * from t5;
+------+
| x |
+------+
| -1 |
| -127 |
| -128 |
+------+
3 rows in set (0.00 sec)
设置严格模式:
更改前:select @@sql_mode;
+------------------------+
| @@sql_mode |
+------------------------+
| NO_ENGINE_SUBSTITUTION | #初始模式
+------------------------+
1 row in set (0.00 sec)
更改:set global sql_mode='strict_trans_tables'; (改后要重启客户端)
mysql> insert into t5 values(128);
ERROR 1264 (22003): Out of range value for column 'x' at row 1 #再次输入会报错
mysql> select @@sql_mode;
+---------------------+
| @@sql_mode |
+---------------------+
| STRICT_TRANS_TABLES | #模式已经发生变化
+---------------------+
1 row in set (0.00 sec)
tinyint整数类型:转变为无符号(0-255)
create table t6 (x tinyint unsigned);
mysql> desc t6;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| x | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.02 sec)
2.int类型:不是限制存储宽度,限制的是显示宽度
create table t7(id int(5));
insert into t7 values(30000);
mysql> select * from t7;
mysql> select * from t7;
+-------+
| id |
+-------+
| 30000 | #id int(5) 是指占5个字符,不够的前面是指是用控制填充的
| 1 |
| 2 |
| 33 |
| 333 |
+-------+
int自带的就是11位(-10总共11位有符号范围),所以不需要指定宽度,默认会有的,最合理
3.浮点型:
格式:float(所有位数,小数位)
create table t8(x float(255,30));
create table t9(x double(255,30));
create table t10(x decimal(65,30));
精确度查看:decimal精度是最高的
insert into t8 values(1.11111111111111111111111);
insert into t9 values(1.11111111111111111111111);
insert into t10 values(1.11111111111111111111111);
mysql> select * from t8;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t9;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
mysql> select * from t10;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
一般情况下用float精度就已经够用了。
4.日期类型:
首先造一个student的文件:设置好文件头应有的属性
create table student(
id int primary key auto_increment,
name char(16),
born_year year,
birth date,
class_time time,
reg_time datetime
);
mysql> desc student;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(16) | YES | | NULL | |
| born_year | year(4) | YES | | NULL | |
| birth | date | YES | | NULL | |
| class_time | time | YES | | NULL | |
| reg_time | datetime | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
增加每个属性的值:
insert into student(name,born_year,birth,class_time,reg_time) values('yzz',now(),now(),now(),now());
查看表格结果:
mysql> select * from student;
+----+------+-----------+------------+------------+---------------------+
| id | name | born_year | birth | class_time | reg_time |
+----+------+-----------+------------+------------+---------------------+
| 1 | yzz | 2018 | 2018-07-19 | 22:26:10 | 2018-07-19 22:26:10 |
+----+------+-----------+------------+------------+---------------------+
1 row in set (0.00 sec)
自己去设置日期和时间:
insert into student(name,born_year,birth,class_time,reg_time) values
('egon1',2000,'2000-11-11',"08:30:00",20171111111111);
#1.不加引号,数字在一块儿,系统默认输出会不全-
#2.如果加引号“08:30:00”,里面按照输入的格式来填写,
输出结果:年-月-日 时:分:秒 这样的格式
5.字符类型:自动补的空格是靠后补齐的
char:定长
varchar:变长
相同点:宽度指的都是最大存储的字符个数,超过了都无法正常存储
不同点:
char(5) :
'm'--->'m '5个字符 #给1个m就补4个字符
varchar(5):
'm'--->'m'1个字符 #给1个就存1个字符
超出范围会报错:
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(2) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> insert into t12(id,name) values(1,'yangzhizong'),(2,'m');
1406 - Data too long for column 'name' at row 1
开启严格模式:超出设定长度会报错
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"
+----+------+
| id | name |
+----+------+
| 1 | ya |
| 2 | m | #右边有空格计算长度时是忽略的
+----+------+
select char_length(name) from t12;
注意点:
1.右边加的空格计算长度时不会自动加进去
2.左边的会自动加进去
insert into t12 (id,name) values(6,' zhangsan'),(7,' lisi'),(8,' yangzhi');
+-------------------+
| char_length(name) |
+-------------------+
| 2 |
| 1 |
| 8 |
| 3 |
| 3 |
| 10 |
| 8 |
| 10 |
+-------------------+
注意:mysql在查询时针对where 字段="值 "会忽略掉右面的空格,即where 字段="值"
左边有空格时候:select * from t12 where name=" yangzhi“ 后面必须要加上空格
右边有空格时候:查询时可以手动加空格,也可以不用加空格
1.上面的查找方式数据精准匹配;
2. 如果时like模糊匹配就不会忽略右面的空格了
+----+------------+
| id | name |
+----+------------+
| 1 | ya |
| 2 | m |
| 3 | mawenjie |
| 4 | mwj |
| 5 | yzz |
| 6 | zhangsan |
| 7 | lisi |
| 8 | yangzhi |
+----+------------+
select * from t12 where name like "m%"; #任意无穷个,所有m开头的
+----+----------+
| id | name |
+----+----------+
| 2 | m |
| 3 | mawenjie |
| 4 | mwj |
+----+----------+
select * from t12 where name like"m_"; #任意一个例如:mi,后面只跟一个字符,即统计所有m开头的2个字符
select * from t12 where name like"m_ _ _"; 可以跟你要查找的位数(下划线之间没有空格,为了说明效果)
varchar:存好存,取不好取,需要在前面另外加一个头进去,例如下面伪代码:
char(5)
egon |axx |lxx |fm |
varchar(5)
1bytes+egon|1bytes+axx|1bytes+lxx|1bytes+fm|
6.枚举与集合类型
枚举enum('a','b','c'):多选一
集合set('a','b','c'):多选多
create table t13(name varchar(15),sex enum('mame','female','unkown'),hobbies set('read','music','play')); 录入
desc t13; 查看
+---------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------------------------+------+-----+---------+-------+
| name | varchar(15) | YES | | NULL | |
| sex | enum('mame','female','unkown') | YES | | NULL | |
| hobbies | set('read','music','play') | YES | | NULL | |
+---------+--------------------------------+------+-----+---------+-------+
记录信息:限定用户从enum枚举、set集合里面里面去选择输入
insert into t13 values('zhangsan','female','play'); 选择单个
insert into t13 values('lisi','mame','music,play,read'); 集合可算选多个
常用sql语句列举:
实例1:新建用户及权限分配
方式一insert方式:PASSWORD是一个加密函数 insert into mysql.user(Host,User,Password) values("localhost","wangwu",PASSWORD("wangwu")); 方式二create方式: create user lisi@localhost identified by "lisi"; create user zhangsan@"%" identified by "zhangsan";
结果如下
#查询结果如下 mysql> select host,user,password from user; +-----------+-------------+-------------------------------------------+ | host | user | password | +-----------+-------------+-------------------------------------------+ | localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | localhost | wangwu | *13D84B9EC321E6D7F2780AD871BF71DFC92E7388 | | localhost | lisi | *2256C4DEC4FD3A53BEB73C77C9ED1135DDF2B8D2 | | % | zhangsan | *D550CDE8CF0F249C0520BF8CFC424D082D87FEF9 | +-----------+-------------+-------------------------------------------+ 7 rows in set (0.00 sec)
实例2:使用enum类型设置SQL可选字段
#SQL字段枚举enum的使用:(例如设置性别) create table t1(id int primary key auto_increment,name varchar(32),sex enum("male","female") not null default "male",age int,address varchar(32));
录入数据后,结果如下
mysql> select * from t1; +----+-------------+--------+------+--------------------------+ | id | name | sex | age | address | +----+-------------+--------+------+--------------------------+ | 1 | yangzhizong | male | 23 | 上海市长浦东新区 | | 2 | mawenjie | female | 21 | 上海市徐汇区 | +----+-------------+--------+------+--------------------------+ 2 rows in set (0.00 sec)