MySQL数据库:是数据库管理软件
MySQL(Structured Query Language):结构化查询语言

MySQL语言分类

  • DDL(数据定义语言):定义数据库、数据表结构,如:create(创建),drop(删除),alter(修改)
  • DML(数据操作语言):主要用于操作数据,如:insert(插入),update(修改),delete(删除)
  • DCL(数据控制语言):定义访问语言,取消访问权限,安全设置
  • DQL(数据查询语言):如:select(查询),from子句,where子句

MySQL数据库的安装和配置

MySQL下载地址: https://www.mysql.com/downloads/

登陆数据库

mysql -u账号 -p密码

或者

mysql -u账号 -p
Enter password:密码

MySQL 创建数据库:

方式一 直接创建数据库:
create database 数据库名;

mysql> create database 方式一的数据库;
Query OK, 1 row affected (0.07 sec)

mysql> show databases;
+-----------------------+
| Database              |
+-----------------------+
| cloud_note            |
| day06                 |
| day07                 |
| db1                   |
| db2                   |
| information_schema    |
| jiapu                 |
| kengchacha            |
| mysql                 |
| netctoss              |
| o2o                   |
| performance_schema    |
| stu                   |
| student               |
| sys                   |
| test                  |
| ttms                  |
| user                  |
| 方式一的数据库        |
+-----------------------+
19 rows in set (0.01 sec)

方式二 创建数据库同时设置字符集:
create database 数据库名 character set 字符集;

方式三 创建数据库同时,设置字符集编码和校对规则:
create database 数据库名 character set 字符集 collate 校对规则;

create database 数据库名 character set 字符集 collate 校对规则

演示:创建一个数据库,命名为“测试”,并设置字符编码为utf8;


mysql> create database 测试 character set utf8;
Query OK, 1 row affected, 1 warning (0.11 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| day06              |
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| user               |
| 测试               |
+--------------------+
10 rows in set (0.11 sec)

数据库删除

drop database 数据库名

演示:删除名为“测试”的数据库;

mysql> drop database 测试;
Query OK, 0 rows affected (0.23 sec)

修改:

alter database 数据库 character set 字符集(如:utf8)

查看数据库:

查看所有数据库show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| cloud_note         |
| day06              |
| day07              |
| db1                |
| db2                |
| information_schema |
| jiapu              |
| kengchacha         |
| mysql              |
| netctoss           |
| o2o                |
| performance_schema |
| stu                |
| student            |
| sys                |
| test               |
| ttms               |
| user               |
+--------------------+
18 rows in set (0.02 sec)
show create database 数据库名;
select database();

切换数据库 use 数据库名

use student  //如,我现在需要将数据库切换到student数据库中

表结构操作:

创建:

create table 表名(
      字段1 字段类型 字段约束,
      字段2 字段类型 字段约束  
);

字段类型:特别注意为cahr(固定长度) / varchar(可变长度)

字段约束:

  • primary key 主键约束
  • unique 唯一约束
  • not null 非空约束
    自增长:auto_increment

删除表:

drop table 表名;

修改表:

alter table 表名 (add,modify,change,drop) ;
rename table 旧表名 to 新表名;
alter table 表名 character set 字符集

查询表结构:

  show tables; 查询出所以的表
  show create table 表名 //表达创建语句,表的定义
  desc 表名  //表结构

MySQL查看数据表

查看表

  • 查看所有的表show tables;
mysql> show tables;
+-----------------+
| Tables_in_day06 |
+-----------------+
| stu             |
+-----------------+
1 row in set (0.00 sec)
  • 查看表的创建过程show create table stu
mysql> show create table stu;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (
  `sid` int(11) NOT NULL,
  `sname` varchar(31) DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • 查看表结构desc stu
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int(11)     | NO   | PRI | NULL    |       |
| sname | varchar(31) | YES  |     | NULL    |       |
| sex   | int(11)     | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改表

mysql> SHOW TABLES FROM mysql;

查看数据表结构

SHOW COLUMNS FROM table_name;
eg:

`mysql> show columns from tb1;`
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| salary   | float(8,2) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

MySql 记录的插入与查找

INSERT

  • 插入记录
  • INSERT[INTO] tbl_name[(col_name,...)] VALUES(val,...)

1、为表中所以字段插入记录:

mysql> INSERT tb1 VALUES('XIAOLI',21,8000);
Query OK, 1 row affected (0.02 sec)

2、为表中特定字段插入记录:

mysql> INSERT tb1(username,age) VALUES('Tom',20);
Query OK, 1 row affected (0.00 sec)

SELECT

  • 记录查找
  • SELECT expr,... FROM tbl_name

1、查看表中所有记录:

mysql> SELECT * FROM tb1;
+----------+------+---------+
| username | age  | salary  |
+----------+------+---------+
| XIAOLI   |   21 | 8000.00 |
| Tom      |   20 |    NULL |
+----------+------+---------+
2 rows in set (0.01 sec)

空值与非空

  • NULL,字段可以为空
  • NOT NULL ,字段值禁止为空
    演示:创建数据表,设置字段nameNOT NULL
mysql> CREATE TABLE tb2(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED NULL
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> show columns from tb2;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

实验1:
插入记录usernameXIAOWANG,ageNULL

mysql> INSERT tb2 VALUES('XIAOWANG',NULL);
Query OK, 1 row affected (0.03 sec)

实验2:
插入记录usernameNULL,age25

mysql> INSERT tb2 VALUES(NULL,25);
ERROR 1048 (23000): Column 'username' cannot be null

错误提示:字段usermane不能为null

AUTO_INCREMENT

  • 自动编号,且必须与主键组合使用
  • 默认情况下,起始值为1,没次的增量为1
    演示:创建一个数据表
    1、错误示范
mysql> CREATE TABLE tb3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT,
    -> username VARCHAR(30) NOT NULL
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

错误提示:必须定义为主键