Mysql-sql话语
Mysql-sql语句
mysql
创建 删除 数据库的三种方法：
方法1
mysql> create database db1; // 创建
mysql> drop database db1; // 删除
方法2
[root@www var]# mkdir db2 // 创建
[root@www var]# chmod 700 db2
[root@www var]# chown mysql.mysql db2
[root@www var]# rm -rf db2 // 删除
方法3
[root@localhost ~]# mysqladmin create db3 // 使用工具mysqladmin创建数据库
[root@localhost ~]# mysqladmin drop db3 // 删除
-------------------------------------------------
帮助
mysql> help
mysql> help create // 查看create命令的帮助
mysql> help create database // 查看create database的帮助
-----------------------------------------------
创建数据库：
>create database db1;
>use db1; // 切换数据库
创建表:
>create table members(id int not null auto_increment primary key,name char(20) not null,tel int(20));
----------------------------------------------------
修改表：
添加字段
>alter table members add qq int(20);
修改字段类型和修饰符
>alter table members modify qq int(10) not null;
>alter table members modify qq int(10) after tel; //字段排序
>alter table members modify qq int(10) first; // 字段排序
修改字段名称、字段类型、修饰符
>alter table members change qq qq int(20) after tel;
>alter table members change qq addr char(20) first;
修改表名称
>alter table members rename t1;
------------------------------------------------
往表里添加记录
> insert into t1(id,name,qq) values(1,"Lucy",773765);
> insert into t1 set id=2,name="John",qq=6656998;
> insert into t1 value(3,"Monica",765687);
查看
> select * from t1;
+----+--------+---------+
| id | name | qq |
+----+--------+---------+
| 1 | Lucy | 773765 |
| 2 | John | 6656998 |
| 3 | Monica | 765687 |
+----+--------+---------+
多条记录一起添加：
> insert into t1 values(4,"Jean",6636677),(5,"Nana",378998),(6,"Hebe",8457839);
--------------------------------------------------------------------
查看：
查看数据库：
>show databases;
查看现在所使用的数据库
>select database();
查看表
>show tables;
查看表的description信息
>desc t1;
查看表的内容
>select * from t1;
条件查询
>select name,qq from t1 where id=3;
>select name,qq from t1 where id=3 or id=4;
查看现在登录的帐号
>select user();
查看日期
>select now();
查看记录个数
>select count(*) from xueke;
查看字段总和
>select sum(math) from xueke;
查看字段平均值
>select avg(math) from xueke;
查看字段最大值
>select max(math) from xueke;
查看字段最小值
>select min(math) from xueke;
根据条件查看
>select count(*) from xueke where gender='male';
分组查看
>select name,gender,count(*) from xueke group by gender;
排序查看
>select * from xueke order by math; // 升序
>select * from xueke order by math desc; // 降序
显示指定的几行
mysql> select * from xueke order by phy desc limit 0,3; // 排序之后显示前三条记录 0表示第一条记录
mysql> select * from xueke order by phy desc limit 3; // 另一种写法
mysql> select * from xueke order by phy desc limit 1,2; // 从第2条记录开始取，取2条记录
---------------------------------------
子查询
>select name,math from xueke where math=(select max(math) from xueke);
模糊查询
>select * from xueke where name like 'w%'; // like模糊查询， w%表示以w开头
>select * from xueke where name like '____'; // 这里是四个_，下滑杠是匹配单个字符
使用正则表达式查询
> select * from xueke where name regexp 'x';
> select * from xueke where name regexp '^w';
---------------------------------------------
更新：
更新记录
>update t1 set name='xiaoqiang' where id=3;
-------------------------------------------
删除：
删除指定记录
>delete from t1 where id=3;
>delete from t1 where id=3 or id=5;
删除所有记录，但是表和字段还存在
>delete from t1;
删除字段
>alter table members drop tel;
删除表
>drop table t1;
删除数据库
>drop database db1;
---------------------------------------
算术操作符：+ - * / %
> select name,math+physical from xueke;
> select 5+5,5-1,5*3,5/2,5%2;
----------------------------------------
比较操作符：
>
>=
<
<=
=
!=
> select 3=3; // 条件为真返回1
> select 3<3; //条件为假返回0
> select 5="5a"; // ""里面的字符当作字符串处理，如果与数字进行比较，则自动将字符串进行转换，然后再进行比较
> select * from xueke where math >= 60;
> select name,(math+physics+computer)/3 from xueke;
> select * from xueke where math < 60 or physics < 60 or computer < 60;
----------------------------------------------------------------
逻辑操作符：
and
or
not
mysql> select not 5=5;
做计算，数据类型自动转换
mysql> select 5+'5a'; // 数据类型的自动转换，5a被自动转换成了5
mysql> select 2+'a2'; // a2被自动转换成了2
mysql> select '5'='5a'; // 因为数据类型一样，不进行转换
-----------------------------------------------
字符类型：
1. 数值
2. 字符串
3. 日期，时间
4. 复合
指定一个合理的数据类型，可以节省内存空间
--------------------------------------------------
整型
tinyint 1个字节 8b
smallint 2个字节
mediumint 3个字节
int 4个字节 32b
bigint 5个字节
tinyint
无符号 从0到255的范围 2的8次方－1＝255
有符号 从－128（－2的7次方）到127（2的7次方－1）
公式：有符号0到２的n次方－１
超出范围，会显示该类型的最大值
例子：
mysql> create table data(fti tinyint,fsi smallint,fmi mediumint,fi int,fbi bigint);
mysql> insert into data values(123456789,123456789,123456789,123456789,123456789);
mysql> select * from data;
+------+-------+---------+-----------+-----------+
| fti | fsi | fmi | fi | fbi |
+------+-------+---------+-----------+-----------+
| 127 | 32767 | 8388607 | 123456789 | 123456789 |
+------+-------+---------+-----------+-----------+
mysql> insert into data values(-123456789,-123456789,-123456789,-123456789,-123456789);
mysql> select * from data;
+------+--------+----------+------------+------------+
| fti | fsi | fmi | fi | fbi |
+------+--------+----------+------------+------------+
| 127 | 32767 | 8388607 | 123456789 | 123456789 |
| -128 | -32768 | -8388608 | -123456789 | -123456789 |
+------+--------+----------+------------+------------+
unsigned指定无符号，zerofill指定用0来填充：
例子：
mysql> create table data1(fti tinyint unsigned,fsi smallint zerofill,fi int zerofill);
mysql> insert into data1 values(10,10,10);
mysql> select * from data1;
+------+-------+------------+
| fti | fsi | fi |
+------+-------+------------+
| 10 | 00010 | 0000000010 |
+------+-------+------------+
mysql> insert into data1 values(-10,-10,-10);
mysql> select * from data1;
+------+-------+------------+
| fti | fsi | fi |
+------+-------+------------+
| 10 | 00010 | 0000000010 |
| 0 | 00000 | 0000000000 |
+------+-------+------------+
mysql> desc data1;
+-------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------------------+------+-----+---------+-------+
| fti | tinyint(3) unsigned | YES | | NULL | |
| fsi | smallint(5) unsigned zerofill | YES | | NULL | |
| fi | int(10) unsigned zerofill | YES | | NULL | |
+-------+-------------------------------+------+-----+---------+-------+
------------------------------------------------------
浮点类型：
mysql> create table data3(abc float(6,2));
mysql> insert into data3 values(1.234),(1234.456),(-1234.789);
mysql> select * from data3;
+----------+
| abc |
+----------+
| 1.23 |
| 1234.46 |
| -1234.79 |
+----------+
---------------------------------------------------
字符串类型：
char 定长的，后面的括号中必须用一个大小修饰符来定义，范围是０到２５５
varchar 不定长的，超出宽度，自动截取
例子：
mysql> insert into data4 values(1,'abc'),(2,'abcdefg');
mysql> select * from data4;
+------+-------+
| id | name |
+------+-------+
| 1 | abc |
| 2 | abcde |
+------+-------+
默认不区分大小写
mysql> select * from data4 where name='ABC';
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
mysql> select * from data4 where binary name='ABC'; // 加上binary区分大小写
mysql> select 'a'='A';
+---------+
| 'a'='A' |
+---------+
| 1 |
+---------+
mysql> select binary 'a'='A';
+----------------+
| binary 'a'='A' |
+----------------+
| 0 |
+----------------+
-------------------------------------------------------------
日期类型：
mysql> create table data5 (d date);
mysql> insert into data5 values('2009-12-20'),('98-01-01'),(20081010),(020808);
mysql> select * from data5;
+------------+
| d |
+------------+
| 2009-12-20 |
| 1998-01-01 |
| 2008-10-10 |
| 2002-08-08 |
+------------+
---------------------------------------------------------------
mysql> create table data6 (t time);
mysql> insert into data6 values('12:12:12'),(1010);
mysql> select * from data6;
+----------+
| t |
+----------+
| 12:12:12 |
| 00:10:10 |
+----------+
-------------------------------------------------------------------------
mysql> create table data8 (dt datetime,ts timestamp);
mysql> insert into data8 values('1999-01-01 10:10:10','1999-01-01 10:10:10'),(20010202121212,20010202121212);
mysql> select * from data8;
+---------------------+---------------------+
| dt | ts |
+---------------------+---------------------+
| 1999-01-01 10:10:10 | 1999-01-01 10:10:10 |
| 2001-02-02 12:12:12 | 2001-02-02 12:12:12 |
+---------------------+---------------------+
mysql> insert into data8 values(now(),null); // 显示当前日期
mysql> select * from data8;
+---------------------+---------------------+
| dt | ts |
+---------------------+---------------------+
| 1999-01-01 10:10:10 | 1999-01-01 10:10:10 |
| 2001-02-02 12:12:12 | 2001-02-02 12:12:12 |
| 2009-12-28 14:17:50 | 2009-12-28 14:17:50 |
+---------------------+---------------------+
---------------------------------------------------------------------------
+------------+
| d |
+------------+
| 2009-12-20 |
| 1998-01-01 |
| 2008-10-10 |
| 2002-08-08 |
+------------+
mysql> select year(d) from data5; 截取年份
+---------+
| year(d) |
+---------+
| 2009 |
| 1998 |
| 2008 |
| 2002 |
+---------+
mysql> select month(d) from data5; 截取月份
+----------+
| month(d) |
+----------+
| 12 |
| 1 |
| 10 |
| 8 |
+----------+
mysql> select day(d) from data5; 截取日
+--------+
| day(d) |
+--------+
| 20 |
| 1 |
| 10 |
| 8 |
+--------+
mysql> select d from data5 where year(d)<2000; // 显示2000年之前的时间
+------------+
| d |
+------------+
| 1998-01-01 |
+------------+
mysql> create table data7 (y year);
mysql> insert into data7 values(98),(00),(09);
-------------------------------------------------------------------
mysql> create table data9 (gender enum('m','f')); 创建这种表的时候必须预先定义好值，插入值的时候，只能插入预先定义好的值
mysql> insert into data9 values('m'),('F'),('ab');
mysql> select * from data9;
+--------+
| gender |
+--------+
| m |
| f |
| |
+--------+
mysql> select * from data9 where gender=2; // 索引定义的第２个值
+--------+
| gender |
+--------+
| f |
+--------+
mysql> select * from data9 where gender=1; // 索引定义的第一个值
+--------+
| gender |
+--------+
| m |
+--------+
mysql> select * from data9 where gender=0; // 错误的值的索引
+--------+
| gender |
+--------+
| |
+--------+
主要用于在一个表单里有性别的时候
----------------------------------------------
跟enum差不多
enum只能从事先定义好的值里选择一个值，但set不同
mysql> create table data10 (type set('a','b','c','d'));
mysql> insert into data10 values('a'),('a,b'),('a,e');
mysql> select * from data10;
+------+
| type |
+------+
| a |
| a,b |
| a |
+------+
mysql
创建 删除 数据库的三种方法：
方法1
mysql> create database db1; // 创建
mysql> drop database db1; // 删除
方法2
[root@www var]# mkdir db2 // 创建
[root@www var]# chmod 700 db2
[root@www var]# chown mysql.mysql db2
[root@www var]# rm -rf db2 // 删除
方法3
[root@localhost ~]# mysqladmin create db3 // 使用工具mysqladmin创建数据库
[root@localhost ~]# mysqladmin drop db3 // 删除
-------------------------------------------------
帮助
mysql> help
mysql> help create // 查看create命令的帮助
mysql> help create database // 查看create database的帮助
-----------------------------------------------
创建数据库：
>create database db1;
>use db1; // 切换数据库
创建表:
>create table members(id int not null auto_increment primary key,name char(20) not null,tel int(20));
----------------------------------------------------
修改表：
添加字段
>alter table members add qq int(20);
修改字段类型和修饰符
>alter table members modify qq int(10) not null;
>alter table members modify qq int(10) after tel; //字段排序
>alter table members modify qq int(10) first; // 字段排序
修改字段名称、字段类型、修饰符
>alter table members change qq qq int(20) after tel;
>alter table members change qq addr char(20) first;
修改表名称
>alter table members rename t1;
------------------------------------------------
往表里添加记录
> insert into t1(id,name,qq) values(1,"Lucy",773765);
> insert into t1 set id=2,name="John",qq=6656998;
> insert into t1 value(3,"Monica",765687);
查看
> select * from t1;
+----+--------+---------+
| id | name | qq |
+----+--------+---------+
| 1 | Lucy | 773765 |
| 2 | John | 6656998 |
| 3 | Monica | 765687 |
+----+--------+---------+
多条记录一起添加：
> insert into t1 values(4,"Jean",6636677),(5,"Nana",378998),(6,"Hebe",8457839);
--------------------------------------------------------------------
查看：
查看数据库：
>show databases;
查看现在所使用的数据库
>select database();
查看表
>show tables;
查看表的description信息
>desc t1;
查看表的内容
>select * from t1;
条件查询
>select name,qq from t1 where id=3;
>select name,qq from t1 where id=3 or id=4;
查看现在登录的帐号
>select user();
查看日期
>select now();
查看记录个数
>select count(*) from xueke;
查看字段总和
>select sum(math) from xueke;
查看字段平均值
>select avg(math) from xueke;
查看字段最大值
>select max(math) from xueke;
查看字段最小值
>select min(math) from xueke;
根据条件查看
>select count(*) from xueke where gender='male';
分组查看
>select name,gender,count(*) from xueke group by gender;
排序查看
>select * from xueke order by math; // 升序
>select * from xueke order by math desc; // 降序
显示指定的几行
mysql> select * from xueke order by phy desc limit 0,3; // 排序之后显示前三条记录 0表示第一条记录
mysql> select * from xueke order by phy desc limit 3; // 另一种写法
mysql> select * from xueke order by phy desc limit 1,2; // 从第2条记录开始取，取2条记录
---------------------------------------
子查询
>select name,math from xueke where math=(select max(math) from xueke);
模糊查询
>select * from xueke where name like 'w%'; // like模糊查询， w%表示以w开头
>select * from xueke where name like '____'; // 这里是四个_，下滑杠是匹配单个字符
使用正则表达式查询
> select * from xueke where name regexp 'x';
> select * from xueke where name regexp '^w';
---------------------------------------------
更新：
更新记录
>update t1 set name='xiaoqiang' where id=3;
-------------------------------------------
删除：
删除指定记录
>delete from t1 where id=3;
>delete from t1 where id=3 or id=5;
删除所有记录，但是表和字段还存在
>delete from t1;
删除字段
>alter table members drop tel;
删除表
>drop table t1;
删除数据库
>drop database db1;
---------------------------------------
算术操作符：+ - * / %
> select name,math+physical from xueke;
> select 5+5,5-1,5*3,5/2,5%2;
----------------------------------------
比较操作符：
>
>=
<
<=
=
!=
> select 3=3; // 条件为真返回1
> select 3<3; //条件为假返回0
> select 5="5a"; // ""里面的字符当作字符串处理，如果与数字进行比较，则自动将字符串进行转换，然后再进行比较
> select * from xueke where math >= 60;
> select name,(math+physics+computer)/3 from xueke;
> select * from xueke where math < 60 or physics < 60 or computer < 60;
----------------------------------------------------------------
逻辑操作符：
and
or
not
mysql> select not 5=5;
做计算，数据类型自动转换
mysql> select 5+'5a'; // 数据类型的自动转换，5a被自动转换成了5
mysql> select 2+'a2'; // a2被自动转换成了2
mysql> select '5'='5a'; // 因为数据类型一样，不进行转换
-----------------------------------------------
字符类型：
1. 数值
2. 字符串
3. 日期，时间
4. 复合
指定一个合理的数据类型，可以节省内存空间
--------------------------------------------------
整型
tinyint 1个字节 8b
smallint 2个字节
mediumint 3个字节
int 4个字节 32b
bigint 5个字节
tinyint
无符号 从0到255的范围 2的8次方－1＝255
有符号 从－128（－2的7次方）到127（2的7次方－1）
公式：有符号0到２的n次方－１
超出范围，会显示该类型的最大值
例子：
mysql> create table data(fti tinyint,fsi smallint,fmi mediumint,fi int,fbi bigint);
mysql> insert into data values(123456789,123456789,123456789,123456789,123456789);
mysql> select * from data;
+------+-------+---------+-----------+-----------+
| fti | fsi | fmi | fi | fbi |
+------+-------+---------+-----------+-----------+
| 127 | 32767 | 8388607 | 123456789 | 123456789 |
+------+-------+---------+-----------+-----------+
mysql> insert into data values(-123456789,-123456789,-123456789,-123456789,-123456789);
mysql> select * from data;
+------+--------+----------+------------+------------+
| fti | fsi | fmi | fi | fbi |
+------+--------+----------+------------+------------+
| 127 | 32767 | 8388607 | 123456789 | 123456789 |
| -128 | -32768 | -8388608 | -123456789 | -123456789 |
+------+--------+----------+------------+------------+
unsigned指定无符号，zerofill指定用0来填充：
例子：
mysql> create table data1(fti tinyint unsigned,fsi smallint zerofill,fi int zerofill);
mysql> insert into data1 values(10,10,10);
mysql> select * from data1;
+------+-------+------------+
| fti | fsi | fi |
+------+-------+------------+
| 10 | 00010 | 0000000010 |
+------+-------+------------+
mysql> insert into data1 values(-10,-10,-10);
mysql> select * from data1;
+------+-------+------------+
| fti | fsi | fi |
+------+-------+------------+
| 10 | 00010 | 0000000010 |
| 0 | 00000 | 0000000000 |
+------+-------+------------+
mysql> desc data1;
+-------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------------------+------+-----+---------+-------+
| fti | tinyint(3) unsigned | YES | | NULL | |
| fsi | smallint(5) unsigned zerofill | YES | | NULL | |
| fi | int(10) unsigned zerofill | YES | | NULL | |
+-------+-------------------------------+------+-----+---------+-------+
------------------------------------------------------
浮点类型：
mysql> create table data3(abc float(6,2));
mysql> insert into data3 values(1.234),(1234.456),(-1234.789);
mysql> select * from data3;
+----------+
| abc |
+----------+
| 1.23 |
| 1234.46 |
| -1234.79 |
+----------+
---------------------------------------------------
字符串类型：
char 定长的，后面的括号中必须用一个大小修饰符来定义，范围是０到２５５
varchar 不定长的，超出宽度，自动截取
例子：
mysql> insert into data4 values(1,'abc'),(2,'abcdefg');
mysql> select * from data4;
+------+-------+
| id | name |
+------+-------+
| 1 | abc |
| 2 | abcde |
+------+-------+
默认不区分大小写
mysql> select * from data4 where name='ABC';
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
mysql> select * from data4 where binary name='ABC'; // 加上binary区分大小写
mysql> select 'a'='A';
+---------+
| 'a'='A' |
+---------+
| 1 |
+---------+
mysql> select binary 'a'='A';
+----------------+
| binary 'a'='A' |
+----------------+
| 0 |
+----------------+
-------------------------------------------------------------
日期类型：
mysql> create table data5 (d date);
mysql> insert into data5 values('2009-12-20'),('98-01-01'),(20081010),(020808);
mysql> select * from data5;
+------------+
| d |
+------------+
| 2009-12-20 |
| 1998-01-01 |
| 2008-10-10 |
| 2002-08-08 |
+------------+
---------------------------------------------------------------
mysql> create table data6 (t time);
mysql> insert into data6 values('12:12:12'),(1010);
mysql> select * from data6;
+----------+
| t |
+----------+
| 12:12:12 |
| 00:10:10 |
+----------+
-------------------------------------------------------------------------
mysql> create table data8 (dt datetime,ts timestamp);
mysql> insert into data8 values('1999-01-01 10:10:10','1999-01-01 10:10:10'),(20010202121212,20010202121212);
mysql> select * from data8;
+---------------------+---------------------+
| dt | ts |
+---------------------+---------------------+
| 1999-01-01 10:10:10 | 1999-01-01 10:10:10 |
| 2001-02-02 12:12:12 | 2001-02-02 12:12:12 |
+---------------------+---------------------+
mysql> insert into data8 values(now(),null); // 显示当前日期
mysql> select * from data8;
+---------------------+---------------------+
| dt | ts |
+---------------------+---------------------+
| 1999-01-01 10:10:10 | 1999-01-01 10:10:10 |
| 2001-02-02 12:12:12 | 2001-02-02 12:12:12 |
| 2009-12-28 14:17:50 | 2009-12-28 14:17:50 |
+---------------------+---------------------+
---------------------------------------------------------------------------
+------------+
| d |
+------------+
| 2009-12-20 |
| 1998-01-01 |
| 2008-10-10 |
| 2002-08-08 |
+------------+
mysql> select year(d) from data5; 截取年份
+---------+
| year(d) |
+---------+
| 2009 |
| 1998 |
| 2008 |
| 2002 |
+---------+
mysql> select month(d) from data5; 截取月份
+----------+
| month(d) |
+----------+
| 12 |
| 1 |
| 10 |
| 8 |
+----------+
mysql> select day(d) from data5; 截取日
+--------+
| day(d) |
+--------+
| 20 |
| 1 |
| 10 |
| 8 |
+--------+
mysql> select d from data5 where year(d)<2000; // 显示2000年之前的时间
+------------+
| d |
+------------+
| 1998-01-01 |
+------------+
mysql> create table data7 (y year);
mysql> insert into data7 values(98),(00),(09);
-------------------------------------------------------------------
mysql> create table data9 (gender enum('m','f')); 创建这种表的时候必须预先定义好值，插入值的时候，只能插入预先定义好的值
mysql> insert into data9 values('m'),('F'),('ab');
mysql> select * from data9;
+--------+
| gender |
+--------+
| m |
| f |
| |
+--------+
mysql> select * from data9 where gender=2; // 索引定义的第２个值
+--------+
| gender |
+--------+
| f |
+--------+
mysql> select * from data9 where gender=1; // 索引定义的第一个值
+--------+
| gender |
+--------+
| m |
+--------+
mysql> select * from data9 where gender=0; // 错误的值的索引
+--------+
| gender |
+--------+
| |
+--------+
主要用于在一个表单里有性别的时候
----------------------------------------------
跟enum差不多
enum只能从事先定义好的值里选择一个值，但set不同
mysql> create table data10 (type set('a','b','c','d'));
mysql> insert into data10 values('a'),('a,b'),('a,e');
mysql> select * from data10;
+------+
| type |
+------+
| a |
| a,b |
| a |
+------+