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 |
+----------+


-------------------------------------------------------------------------------------------------------------

datetime类型和timestamp时间戳类型，可以把当前的日期插入到他所修饰的字段里，年月日分钟小时秒

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 |
+---------------------+---------------------+


---------------------------------------------------------------------------------------------------------------

mysql> select * from data5;
+------------+
| 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);


-------------------------------------------------------------------------------------------------------

enum（复合类型）枚举类型

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 |
+--------+
|        |
+--------+

主要用于在一个表单里有性别的时候


----------------------------------------------------------------------------------

set类型　
跟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    |
+------+