mysql操作命令（3）

mysql操作命令(3)

show databases;在任何地方都能使用

 

mysql> show tables;

ERROR 1046 (3D000): No database selected

只有选中了库才能使用

 

修改默认字符集

alter table 表名 default charset=字符集;

 

mysql> create table demo(

    -> id int unsigned not null auto_increment primary key,

    -> name varchar(20) not null unique,

    -> num int(4) unsigned zerofill

-> );

 

mysql> select * from demo;

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

| id | name | num  |

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

|  1 | qq   | 0027 |

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

 

mysql> create table t1(age tinyint(2));

Query OK, 0 rows affected (0.06 sec)

 

mysql> insert into t1 value(100);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from t1;

+------+

| age  |

+------+

|  100 |

+------+

 

修改字段名

alter table 表名 change 原字段名 新字段名 类型、约束等其他关于列的定义;

alter table demo change name username varchar(16) not null unique;

 

mysql> #在username后面添加一个字段

mysql> alter table demo add age tinyint unsigned not null after username;

alter table 表名 add 新列名 列名相关定义 after 参考列名;

 

mysql> #在表的最前面添加一个字段名

mysql> alter table demo add mm int not null first;

Alter table 原表名 add 新列名 新列名的定义 first;

 

mysql> #删除指定字段信息

mysql> alter table demo drop mm;

Alter table 表名 drop 要被删除的列名;

 

mysql> #给指定字段添加索引

mysql> alter table demo add index index_age(age);

Alter table 表名 add index 索引名(被索引的字段)

 

mysql> #查看某一张表上的所有的索引

mysql> show indexes from demo\G

show indexes from 表名

*************************** 1. row ***************************

        Table: demo

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: id

    Collation: A

  Cardinality: 5

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 2. row ***************************

        Table: demo

   Non_unique: 0

     Key_name: name

 Seq_in_index: 1

  Column_name: username

    Collation: A

  Cardinality: 5

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 3. row ***************************

        Table: demo

   Non_unique: 0

     Key_name: username

 Seq_in_index: 1

  Column_name: username

    Collation: A

  Cardinality: 5

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 4. row ***************************

        Table: demo

   Non_unique: 1

     Key_name: index_age

 Seq_in_index: 1

  Column_name: age

    Collation: A

  Cardinality: NULL

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

 

mysql> #使用trancate删除数据

mysql> truncate table stu;

Truncate table 表名

 

mysql> #复制一张表

mysql> create table cp_stu like stu;

Create table 新表 like 被复制的表

 

复制表中的数据，插入到另外一张表张

mysql> insert into cp_s1 select * from s1;

 

快速制作一张大数据表

insert into cp_s1 select * from cp_s1

 

mysql> #计算一张表中有多少条记录

mysql> select count(*) from cp_s1;

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

| count(*) |

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

| 12582912 |

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

 

mysql> #delete删除表中的数据

mysql> delete from cp_s1;

Query OK, 12582912 rows affected (0.01 sec)

 

mysql> #truncate 删除表中的数据

mysql> truncate s1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from stu;

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

| id | name     | sex | age | classid |

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

|  1 | lisi     | m   |  20 | class3  |

|  2 | kulianer | w   |  16 | class3  |

|  3 | daozhang | w   |  66 | class5  |

|  4 | yy       | m   |  18 | class4  |

|  5 | ww       | m   |  28 | class2  |

|  6 | aa       | m   |  20 | class3  |

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

mysql> #查看某一个字段的数据

mysql> select name from stu;

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

| name     |

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

| aa       |

| daozhang |

| kulianer |

| wangwu   |

| ww       |

| yy       |

| zhaoliu  |

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

7 rows in set (0.00 sec)

 

mysql> #查看若干字段的数据

mysql> select name,age from stu;

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

| name     | age |

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

| zhaoliu  |  19 |

| wangwu   |  21 |

| kulianer |  16 |

| daozhang |  66 |

| yy       |  18 |

| ww       |  28 |

| aa       |  20 |

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

7 rows in set (0.00 sec)

 

mysql> #给字段起别名

mysql> select name username from stu;

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

| username |

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

| aa       |

| daozhang |

| kulianer |

| wangwu   |

| ww       |

| yy       |

| zhaoliu  |

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

7 rows in set (0.00 sec)

 

mysql>  #查询所有学生信息  多查出一列（年龄+10）起个别名age2

mysql> select *,age+10 age2 from stu;

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

| id | name     | sex | age | classid | age2 |

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

|  1 | zhaoliu  | w   |  19 | class2  |   29 |

|  2 | wangwu   | m   |  21 | class3  |   31 |

|  3 | kulianer | w   |  16 | class3  |   26 |

|  4 | daozhang | w   |  66 | class5  |   76 |

|  5 | yy       | m   |  18 | class4  |   28 |

|  6 | ww       | m   |  28 | class2  |   38 |

|  7 | aa       | m   |  20 | class3  |   30 |

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

7 rows in set (0.00 sec)

 

mysql> 追加一个字段信息  city  值都为 guangzhou

    -> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

mysql> #追加一个字段信息  city  值都为 guangzhou ;

mysql> select * , "guangzhou" city from stu;

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

| id | name     | sex | age | classid | city      |

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

|  1 | zhaoliu  | w   |  19 | class2  | guangzhou |

|  2 | wangwu   | m   |  21 | class3  | guangzhou |

|  3 | kulianer | w   |  16 | class3  | guangzhou |

|  4 | daozhang | w   |  66 | class5  | guangzhou |

|  5 | yy       | m   |  18 | class4  | guangzhou |

|  6 | ww       | m   |  28 | class2  | guangzhou |

|  7 | aa       | m   |  20 | class3  | guangzhou |

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

7 rows in set (0.00 sec)

 

mysql> #将两个字段的数据连接起来;

mysql> select concat('aa','bb');

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

| concat('aa','bb') |

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

| aabb              |

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

1 row in set (0.00 sec)

 

mysql> #将stu表中的班级和姓名字段合并到一列输出  并起子别名

mysql> select concat(classid,':','name') result from stu;

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

| result      |

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

| class2:name |

| class3:name |

| class3:name |

| class5:name |

| class4:name |

| class2:name |

| class3:name |

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

7 rows in set (0.00 sec)

 

mysql>  #年龄在20-30之间的学生信息

mysql> select * from stu where age >=20 and age<=30;

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

| id | name   | sex | age | classid |

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

|  2 | wangwu | m   |  21 | class3  |

|  6 | ww     | m   |  28 | class2  |

|  7 | aa     | m   |  20 | class3  |

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

3 rows in set (0.00 sec)

 

mysql>  #查询id是 3 4 7

mysql> select * from stu where id in(3,4,7);

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

| id | name     | sex | age | classid |

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

|  3 | kulianer | w   |  16 | class3  |

|  4 | daozhang | w   |  66 | class5  |

|  7 | aa       | m   |  20 | class3  |

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

3 rows in set (0.00 sec)

 

mysql> #查询class3中所有男生的信息

mysql> select * from stu where sex='m' and classid='class3';

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

| id | name   | sex | age | classid |

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

|  2 | wangwu | m   |  21 | class3  |

|  7 | aa     | m   |  20 | class3  |

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

2 rows in set (0.00 sec)

 

mysql> #查询性别为m的03和02班期学生信息

mysql> select * from sex='m' and classid in('class3','class2');

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

mysql> select * from stu where sex='m' and classid in('class3','class2');

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

| id | name   | sex | age | classid |

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

|  2 | wangwu | m   |  21 | class3  |

|  6 | ww     | m   |  28 | class2  |

|  7 | aa     | m   |  20 | class3  |

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

3 rows in set (0.00 sec)

 

mysql> #查询不重复的班级名称

mysql> select distinct classid from stu;

+---------+

| classid |

+---------+

| class2  |

| class3  |

| class5  |

| class4  |

+---------+

4 rows in set (0.00 sec)

 

mysql> #查询学生中学号不是1 3 5 的

mysql> select * from stu where id not in(1,3,5);

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

| id | name     | sex | age | classid |

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

|  2 | wangwu   | m   |  21 | class3  |

|  4 | daozhang | w   |  66 | class5  |

|  6 | ww       | m   |  28 | class2  |

|  7 | aa       | m   |  20 | class3  |

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

4 rows in set (0.00 sec)

 

mysql> #查询以“zhao”开头的学生信息

mysql> select * from stu where name like 'zhao%';

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

| id | name    | sex | age | classid |

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

|  1 | zhaoliu | w   |  19 | class2  |

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

1 row in set (0.00 sec)

 

mysql> #以“u”结尾

mysql> select * from stu where name like '%u';

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

| id | name    | sex | age | classid |

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

|  1 | zhaoliu | w   |  19 | class2  |

|  2 | wangwu  | m   |  21 | class3  |

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

2 rows in set (0.00 sec)

 

mysql> #姓名中包含特定字母的om st

mysql> select * from stu where name like '%l%';

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

| id | name     | sex | age | classid |

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

|  1 | zhaoliu  | w   |  19 | class2  |

|  3 | kulianer | w   |  16 | class3  |

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

2 rows in set (0.00 sec)

 

mysql> select * from stu where name like '%i%';

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

| id | name     | sex | age | classid |

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

|  1 | zhaoliu  | w   |  19 | class2  |

|  3 | kulianer | w   |  16 | class3  |

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

2 rows in set (0.00 sec)

 

mysql> #每个“_”表示一个字符（具体的模糊查询）

mysql> select * from stu where name like '__';

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

| id | name | sex | age | classid |

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

|  5 | yy   | m   |  18 | class4  |

|  6 | ww   | m   |  28 | class2  |

|  7 | aa   | m   |  20 | class3  |

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

3 rows in set (0.00 sec)

 

mysql> select * from stu where name like '_______';

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

| id | name    | sex | age | classid |

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

|  1 | zhaoliu | w   |  19 | class2  |

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

1 row in set (0.00 sec)

 

mysql> #按班级分组 并且统计  每个班的人数 最大年龄  最小年龄

mysql> select classid , count(*) , max(age) , min(age) from stu group by classid

    -> ;

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

| classid | count(*) | max(age) | min(age) |

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

| class2  |        2 |       28 |       19 |

| class3  |        3 |       21 |       16 |

| class4  |        1 |       18 |       18 |

| class5  |        1 |       66 |       66 |

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

4 rows in set (0.00 sec)