mysql的基本操作---------库的操作和表的操作

mysql的关闭和启动

使用mysql首先要打开它的服务器连接上

基本操作（库的操作）

基本指令：
create database【if not exists】 库名; //创建数据库
show create database; //显示数据库创建语句
drop database[if exists] 库名; //删除数据库（尽量不要随便删除，就不做演示）
alter database[if exists] 库名; //修改数据库

1、创建数据库、查看所有的数据库

其中if not exists是为了检测是否已经创建过同名的数据库，如果有就会有提示：加上这一点只会有警告，不加的话就直接报错。
2、字符集的引入
上图中创建了数据库，会显示Query OK, 1 row affected (0.00 sec)说明创建数据库没有指定字符集和校验规则，系统使用默认字符集：utf8校验规则是：utf8_general_ci
mysql> show charset;//查看所有的字符集
校验规则会影响什么（大小写和排序）

1>区分大小写
（1）创建一个不区分大小写的数据库：

`mysql> use company;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table person(name varchar(20));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into person (name)values('a');
Query OK, 1 row affected (0.04 sec)

mysql> insert into person (name)values('A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person (name)values('b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person (name)values('B');
Query OK, 1 row affected (0.00 sec)

mysql> select* from person where name='a';
+------+
| name |
+------+
| a    |
| A    |
+------+
2 rows in set (0.03 sec)

mysql> 

（2）创建一个库使用utf8_genaral_bin(区分大小写）

mysql> create database mytest collate utf8_bin;
Query OK, 1 row affected (0.00 sec)
mysql> use mytest;
Database changed
mysql> create table person(name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into person (name)values('A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person (name)values('a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person (name)values('B');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person (name)values('b');
Query OK, 1 row affected (0.00 sec)

mysql> select* from person where name='a';
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> 

2>影响排序
company校验规则按照插入前后顺序排序

mysql> use company;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select *from person order by name;
+------+
| name |
+------+
| a    |
| A    |
| b    |
| B    |
+------+
4 rows in set (0.00 sec)

mysql> 

mytest数据库按大小写排序

mysql> use mytest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select* from person order by name;
+------+
| name |
+------+
| A    |
| B    |
| a    |
| b    |
+------+
4 rows in set (0.00 sec)

mysql> 

3、修改数据库（主要指修改字符集，校验规则）
将mytest数据库的字符集修改成gbk;

mysql> alter database mytest charset=gbk;
Query OK, 1 row affected (0.00 sec)

mysql> show create database mytest;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mytest   | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

表的操作

创建表
create table 表名（字段1(类型），字段2（类型）…
）character set 字符集 collate 校验规则 engine 存储引擎；
修改表
1、修改时添加字段：alter table student add phone varchar(20) ;

mysql> use mytest;
Database changed
mysql> create table student(
    -> name varchar(20),
    -> age int,
    -> sex char;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
mysql> create table student( name varchar(20), age int, sex char)character set utf8 engine MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table student 
    -> add  phone varchar(20) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

2、修改字段类型的大小 例如：alter table student modify name varchar(60);

3、修改表名:

4、修改字符集:alter table users charset=gbk;
5、修改列名:将name修改成xingming;

插入操作

表的查询
select* from 表名;//查看整个表
select* from 表名 where 姓名=’h’;//查看姓名是h的一行

删除操作
删除字段：

数据库的备份与恢复