mysql基本使用-白红宇

打开mysql: mysql -hlocalhost -uroot -p密码

接下来就是高能了，有时候会因为mysql版本不同的问题，字段的问题，空格的问题经常造成错误，所以以下的一些代码在你的机器上出错请不要见怪，不懂的就去问百度。

表演开始：

先创建一个列表：

mysql> create table student(

-> stu_id int auto_increment,

-> name CHAR(32) NOT NULL,

-> age INT NOT NULL,

-> register_date date not null,

-> primary key (id));

ERROR 1046 (3D000): No database selected

what?出错了，怎么办，原因是什么

我们要先创建一个数据库

mysql> create database xsphpdb;

Query OK, 1 row affected (0.00 sec)

ok，再这创建一个列表

mysql> create table xsphpdb.users(

-> id int,

-> name char(30),

-> age int,sex char(3));

Query OK, 0 rows affected (0.08 sec)

我们再来看看这个列表

mysql> show tables;

ERROR 1046 (3D000): No database selected

what?又出错了，

因为你虽然创建了一个数据库，却没有进入它，故而出错。

mysql> use xsphpdb;

Database changed

mysql> show tables;

+-------------------+

| Tables_in_xsphpdb |

+-------------------+

| users |

+-------------------+

1 row in set (0.02 sec)

this is ok ,那么我们接下来做什么呢？

查看一个列表的详细信息

mysql> desc users;

+-------+----------+------+-----+---------+-------+

+-------+----------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| sex | char(3) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

再来创建一个：

mysql> create table studen(

-> stu_id INT NOT NULL AUTO_INCREMENT,

-> name CHAR(32) NOT NULL,

-> age INT NOT NULL,

-> register_date DATE not null,

-> PRIMARY KEY ( stu_id )

-> );

mysql> desc studen;

+---------------+----------+------+-----+---------+----------------+

+---------------+----------+------+-----+---------+----------------+

| stu_id | int(11) | NO | PRI | NULL | auto_increment |

| age | int(11) | NO | | NULL | |

+---------------+----------+------+-----+---------+----------------+

大家看这个表，跟上面的表有什么区别？

在于NUll

记住以后创建列表一定要加

-> stu_id INT NOT NULL AUTO_INCREMENT,

-> name CHAR(32) NOT NULL,

-> age INT NOT NULL,

-> register_date DATE not null,

not null ，否则你将迎来的是各种错误

插入数据： insert into studen (name,age,register_date) values ("alex li",22,"2016-03-4");

select * from studen;

查看元素

+--------+---------+-----+---------------+

+--------+---------+-----+---------------+

| 1 | alex li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

1 row in set (0.00 sec)

如此重复几遍

mysql> select * from studen;

+--------+---------+-----+---------------+

+--------+---------+-----+---------------+

| 1 | alex li | 22 | 2016-03-04 |

| 2 | alex li | 22 | 2016-03-04 |

| 3 | alex li | 22 | 2016-03-04 |

| 4 | alex li | 22 | 2016-03-04 |

| 5 | alex li | 22 | 2016-03-04 |

| 6 | alex li | 22 | 2016-03-04 |

| 7 | alex li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

如何查数据呢

mysql> select * from studen limit 3 offset 2;

+--------+---------+-----+---------------+

+--------+---------+-----+---------------+

| 3 | alex li | 22 | 2016-03-04 |

| 4 | alex li | 22 | 2016-03-04 |

| 5 | alex li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

mysql> select * from studen limit 1 offset 2;

+--------+---------+-----+---------------+

+--------+---------+-----+---------------+

| 3 | alex li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

mysql> select * from studen where stu_id>3 and age=22;

+--------+---------+-----+---------------+

+--------+---------+-----+---------------+

| 4 | alex li | 22 | 2016-03-04 |

| 5 | alex li | 22 | 2016-03-04 |

| 6 | alex li | 22 | 2016-03-04 |

| 7 | alex li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

4 rows in set (0.00 sec)

此处，还有一个特别的概念：

模糊寻找：

mysql> select * from studen where register_date like "2016-03%";

+--------+---------+-----+---------------+

+--------+---------+-----+---------------+

| 1 | alex li | 22 | 2016-03-04 |

| 2 | alex li | 22 | 2016-03-04 |

| 3 | alex li | 22 | 2016-03-04 |

| 4 | alex li | 22 | 2016-03-04 |

| 5 | alex li | 22 | 2016-03-04 |

| 6 | alex li | 22 | 2016-03-04 |

| 7 | alex li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

7 rows in set, 1 warning (0.00 sec)

增，查，学完了，我们来学习如何修改：

update studen set name="ChenRonghua",age=33 where stu_id=4;

update studen set name="ChenRonghua",age=33 where stu_id>6;

mysql> select * from studen where register_date like "2016-03%";

+--------+-------------+-----+---------------+

+--------+-------------+-----+---------------+

| 1 | alex li | 22 | 2016-03-04 |

| 2 | alex li | 22 | 2016-03-04 |

| 3 | alex li | 22 | 2016-03-04 |

| 4 | ChenRonghua | 33 | 2016-03-04 |

| 5 | alex li | 22 | 2016-03-04 |

| 6 | alex li | 22 | 2016-03-04 |

| 7 | ChenRonghua | 33 | 2016-03-04 |

+--------+-------------+-----+---------------+

最后一个是删

delete from studen where name="ChenRonghua"；

还有一个就是如何排序的问题

正着排序，与反着排序

mysql> select * from studen order by stu_id;

+--------+---------+-----+---------------+

+--------+---------+-----+---------------+

| 1 | alex li | 22 | 2016-03-04 |

| 2 | alex li | 22 | 2016-03-04 |

| 3 | alex li | 22 | 2016-03-04 |

| 5 | alex li | 22 | 2016-03-04 |

| 6 | alex li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

5 rows in set (0.00 sec)

mysql> select * from studen order by stu_id desc;

+--------+---------+-----+---------------+

+--------+---------+-----+---------------+

| 6 | alex li | 22 | 2016-03-04 |

| 5 | alex li | 22 | 2016-03-04 |

| 3 | alex li | 22 | 2016-03-04 |

| 2 | alex li | 22 | 2016-03-04 |

| 1 | alex li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

再添加两个数据

mysql> insert into studen (name,age,register_date) values ("wngd",23,"2016-06-4");

Query OK, 1 row affected (0.00 sec)

mysql> insert into studen (name,age,register_date) values ("wn45",2324,"2016-07-4");

Query OK, 1 row affected (0.00 sec)

mysql> select * from studen;

+--------+---------+------+---------------+

+--------+---------+------+---------------+

| 1 | alex li | 22 | 2016-03-04 |

| 2 | alex li | 22 | 2016-03-04 |

| 3 | alex li | 22 | 2016-05-31 |

| 5 | alex li | 22 | 2016-03-04 |

| 6 | alex li | 22 | 2016-03-04 |

| 8 | wngd | 23 | 2016-06-04 |

| 9 | wn45 | 2324 | 2016-07-04 |

+--------+---------+------+---------------+

7 rows in set (0.00 sec)

对数据进行统计

mysql> select name,count(*) as stu_num from studen group by register_date;

+---------+---------+

| name | stu_num |

+---------+---------+

| alex li | 4 |

| alex li | 1 |

| wngd | 1 |

| wn45 | 1 |

+---------+---------+

4 rows in set (0.00 sec)

mysql> select name,sum(age) from studen;

+---------+----------+

| name | sum(age) |

+---------+----------+

| alex li | 2457 |

+---------+----------+

1 row in set (0.00 sec)

求总和

mysql> select name,sum(age) from studen group by name;

+---------+----------+

| name | sum(age) |

+---------+----------+

| alex li | 110 |

| wn45 | 2324 |

| wngd | 23 |

+---------+----------+

mysql> select name,sum(age) from studen group by name with rollup;

+---------+----------+

| name | sum(age) |

+---------+----------+

| alex li | 110 |

| wn45 | 2324 |

| wngd | 23 |

| NULL | 2457 |

+---------+----------+

4 rows in set (0.00 sec)

mysql> select coalesce(name,"total age"),sum(age) from studen group by name with rollup;

+----------------------------+----------+

| coalesce(name,"total age") | sum(age) |

+----------------------------+----------+

| alex li | 110 |

| wn45 | 2324 |

| wngd | 23 |

| total age | 2457 |

+----------------------------+----------+

再给大家介绍一下：

mysql> ALTER TABLE studen ADD zk_en VARCHAR(16) not null; #加一列一定要加not null,否则以后的操作很难办，由于版本不同，各解决方法也不尽相同

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc studen;

+---------------+---------------+------+-----+---------+----------------+

+---------------+---------------+------+-----+---------+----------------+

| stu_id | int(11) | NO | PRI | NULL | auto_increment |

| age | int(11) | NO | | NULL | |

| sex | enum('M','F') | YES | | NULL | |

| phone | int(11) | NO | | NULL | |

+---------------+---------------+------+-----+---------+----------------+

mysql> alter table studen change zk_en gender char(32) not null default "X";#修改类型

Query OK, 7 rows affected (0.07 sec)

Records: 7 Duplicates: 0 Warnings: 0

mysql> desc studen;

+---------------+---------------+------+-----+---------+----------------+

+---------------+---------------+------+-----+---------+----------------+

| stu_id | int(11) | NO | PRI | NULL | auto_increment |

| age | int(11) | NO | | NULL | |

| sex | enum('M','F') | YES | | NULL | |

| phone | int(11) | NO | | NULL | |

| gender | char(32) | NO | | X | |

+---------------+---------------+------+-----+---------+----------------+

但是，如果还是不够怎么办呢

我们学习怎么把zk_env null改变呢

update studen set zk_ens=0 where zk_ens is null;

alter table studen MODIFY COLUMN zk_ens int(11) NOT NULL DEFAULT '0' ;

this is ok

本文转自眉间雪 51CTO博客，原文链接：http://blog.51cto.com/13348847/2046693，如需转载请自行联系原作者