MySQL浅学

2017-01-13 10:47:01来源:oschina作者:聆听朝阳的美好人点击

第七城市th7cn
一、MySQL简介

MySQL是一种小型关系型数据库,被广泛用于中小企业。其主要特点就是开放源码。其它基础查看MySQL百度百科(如果想多做了解)。


二、MySQL基本使用
1、登录MySQL

在MySQL安装时如果设置了密码,那么直接执行命令登录就可以了。如果没有设置密码,需要先查看MySQL初始密码,执行如下指令(MySQL安装成功的前提下)。


sudo tail /etc/mysql/debian.cnf

password后边的字符串就是MySQL初始密码。①登录MySQL


mysql -u root(用户名) -p(密码,不要有空格,或者-p后直接enter键再输入密码)

输入MySQL登录密码即可。②修改MySQL下root用户登录密码


不需要先登录mysql就可修改密码
mysqladmin -u root -p password (要修改的密码)
登录mysql后修改密码
UPDATE user SET password=PASSWORD('123456') WHERE user='root';
FLUSH PRIVILEGES;

或者


SET PASSWORD FOR root=PASSWORD('123456');
MySQL启动和关闭
service mysql start MySQL启动 service mysql stop MySQL停止
2、MySQL使用
>查看初始数据库
mysql> show databases; +--------------------+
| Database | +--------------------+
| information_schema | | mysql | | performance_schema |
| sys | +--------------------+
4 rows in set (0.00 sec)
>数据库的CURD(增删改查)
1、数据库创建

①创建名为Test的数据库


mysql> create database Test;
Query OK, 1 row affected (0.01 sec)
mysql> show databases; +--------------------+
| Database | +--------------------+
| information_schema | | Test | | mysql | | performance_schema |
| sys | +--------------------+
5 rows in set (0.00 sec)

② 创建一个使用utf-8字符集的Test2数据库(解决数据库中存入汉字不能正常显示的问题)。


mysql> create database Test2 character set utf8;
Query OK, 1 row affected (0.00 sec)

③创建一个使用utf-8字符集,并带校对规则的Test3数据库。会对存入的数据进行检查。


mysql> create database Test3 character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

③显示创建数据库的语句信息


mysql> show create database Test
show create database Test
mysql> show create database Test3; +----------+----------------------------------------------------------------+
| Database | Create Database | +----------+----------------------------------------------------------------+
| Test3 | CREATE DATABASE `Test3` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
2、数据库的删除

删除数据库Test3


mysql> drop database Test3;
Query OK, 0 rows affected (0.05 sec)
3、数据库的修改

修改数据库(将Test的字符集设为 utf8,默认语言集是 latin1)


mysql> alter database Test character set utf8;
Query OK, 1 row affected (0.04 sec)
4、数据库的查看

查看MySQL中有哪些数据库。


mysql> show databases; +--------------------+
| Database | +--------------------+
| information_schema | | Test | | Test2 | | mysql | | performance_schema |
| sys | +--------------------+
6 rows in set (0.00 sec)
>数据库中表的CURD

使用一个数据库(使用Test2)


mysql> use Test2
Database changed
1、创建表

创建表的名字为 people ,表中分别有 id 和 name 字段,字段类型分别为 int 和 varchar()。


mysql> create table people(id int, name varchar(20));
Query OK, 0 rows affected (0.21 sec)

查看刚刚创建表的表结构


mysql> desc people; +-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+
| id| int(11) | YES| | NULL||
| name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MySQL中的数据类型这里写图片描述


bit:1位  可以指定位数,如:bit(3)int:2字节 可以指定最大位数,如:int<4> 最大为4位的整数float:2个字节 可以指定最大的位数和最大的小数位数(如:float<5,2> 最大为一个5位的数,小数位最多2位 )double:4个字节 可以指定最大的位数和最大的小数位数,如:float<6,4>:最大为一个6位的数,小数位最多4位char:必须指定字符数,如char(5) 为不可变字符 即使存储的内容为’ab’,也是用5个字符的空间存储这个数据varchar:必须指定字符数,如varchar(5) 为可变字符 如果存储的内容为’ab’,占用2个字符的空间;如果为’abc’,则占用3个字符的空间text:大文本(大字符串)blob:二进制大数据 如图片,音频文件,视频文件date:日期 如:’1921-01-02’datetime:日期+时间 如:’1921-01-02 12:23:43’timeStamp:时间戳,自动赋值为当前日期时间查看指定表的创建语句


mysql> show create table people /G;
*************************** 1. row ***************************
Table: people
Create Table: CREATE TABLE `people` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
2、修改表

修改表名


mysql> show tables; +-----------------+
| Tables_in_Test2 | +-----------------+
| people | +-----------------+
1 row in set (0.00 sec)
mysql> rename table people to employee;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables; +-----------------+
| Tables_in_Test2 | +-----------------+
| employee | +-----------------+
1 row in set (0.00 sec)

增加一个字段


mysql> desc employee; +-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+
| id| int(11) | YES| | NULL||
| name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table employee add column height double;
Query OK, 0 rows affected (0.25 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> desc employee; +--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+
| id | int(11) | YES| | NULL||
| name | varchar(20) | YES| | NULL||
| height | double | YES | | NULL | | +--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改一个字段


mysql> desc employee; +--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+
| id | int(11) | YES| | NULL||
| name | varchar(20) | YES| | NULL||
| height | double | YES | | NULL | | +--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table employee modify column height float;
Query OK, 0 rows affected (0.10 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> desc employee; +--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+
| id | int(11) | YES| | NULL||
| name | varchar(20) | YES| | NULL||
| height | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除一个字段


mysql> desc employee; +--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+
| id | int(11) | YES| | NULL||
| name | varchar(20) | YES| | NULL||
| height | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table employee drop column height;
Query OK, 0 rows affected (0.06 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> desc employee; +-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+
| id| int(11) | YES| | NULL||
| name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec) 3、删除表
mysql> drop table employee;
Query OK, 0 rows affected (0.01 sec)
mysql> desc employee;
ERROR 1146 (42S02): Table 'Test2.employee' doesn't exist
4、显示指定数据库中所有表

查看有哪些数据库


mysql> show databases; +--------------------+
| Database | +--------------------+
| information_schema | | Test | | Test2 | | mysql | | performance_schema |
| sys | +--------------------+
6 rows in set (0.00 sec)

我们查看数据库 mysql 中有哪些表(显示部分)


mysql> use mysql;
Database changed
mysql> show tables; +---------------------------+
| Tables_in_mysql | +---------------------------+
| columns_priv | | db | | engine_cost |
| event |
| func|
| general_log | | user | +---------------------------+ 31 rows in set (0.00 sec)

查看 user 表的数据结构(数据太多,只显示部分吧)


mysql> desc user /G;
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: User
Type: char(32)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: Select_priv
Type: enum('N','Y')
Null: NO
Key:
Default: N
Extra:
45 rows in set (0.00 sec)
ERROR:
No query specified
>表数据的CRUD

创建一个员工表


mysql> create table employee(id int, name varchar(20),sex int, birthday date, salary double, entry_data date, resume text);
Query OK, 0 rows affected (0.31 sec)

在 employee 员工表中插入一条数据


mysql> insert into employee values(1, 'zhangsan', 1, '1983-04-11', 30000, '1999-12-01','THis is first insert');
Query OK, 1 row affected (0.05 sec)

更新数据(update)将员工表中所有人薪水加500


mysql> select * from employee /G;
*************************** 1. row ***************************
id: 1
name: zhangsan
sex: 1
birthday: 1983-04-11
salary: 30000
entry_data: 1999-12-01
resume: THis is first insert
*************************** 2. row ***************************
id: 2
name: lisi
sex: 1
birthday: 1983-04-11
salary: 30000
entry_data: 1999-12-01
resume: THis is secord insert
*************************** 3. row ***************************
id: 3
name: wangwu
sex: 1
birthday: 1983-04-11
salary: 30000
entry_data: 1999-12-01
resume: 3th insert
*************************** 4. row ***************************
id: 3
name: zhaoliu
sex: 1
birthday: 1983-04-11
salary: 8000
entry_data: 1999-12-01
resume: 4th insert
*************************** 5. row ***************************
id: 5
name: okk
sex: 1
birthday: 1983-04-11
salary: 10000
entry_data: 1999-12-01
resume: 5th insert
5 rows in set (0.00 sec)
ERROR:
No query specified
#############################################################################
mysql> update employee set salary = salary + 500;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5Changed: 5Warnings: 0
mysql> select*from employee /G;
*************************** 1. row ***************************
id: 1
name: zhangsan
sex: 1
birthday: 1983-04-11
salary: 30500
entry_data: 1999-12-01
resume: THis is first insert
*************************** 2. row ***************************
id: 2
name: lisi
sex: 1
birthday: 1983-04-11
salary: 30500
entry_data: 1999-12-01
resume: THis is secord insert
*************************** 3. row ***************************
id: 3
name: wangwu
sex: 1
birthday: 1983-04-11
salary: 30500
entry_data: 1999-12-01
resume: 3th insert
*************************** 4. row ***************************
id: 3
name: zhaoliu
sex: 1
birthday: 1983-04-11
salary: 8500
entry_data: 1999-12-01
resume: 4th insert
*************************** 5. row ***************************
id: 5
name: okk
sex: 1
birthday: 1983-04-11
salary: 10500
entry_data: 1999-12-01
resume: 5th insert
5 rows in set (0.00 sec)
ERROR:
No query specified
##############################################################################
mysql> update employee set salary = salary + 100 where name = 'zhangsan';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql> select*from employee /G;
*************************** 1. row ***************************
id: 1
name: zhangsan
sex: 1
birthday: 1983-04-11
salary: 30600
entry_data: 1999-12-01
resume: THis is first insert
*************************** 2. row ***************************
id: 2
name: lisi
sex: 1
birthday: 1983-04-11
salary: 30500
entry_data: 1999-12-01
resume: THis is secord insert
*************************** 3. row ***************************
id: 3
name: wangwu
sex: 1
birthday: 1983-04-11
salary: 30500
entry_data: 1999-12-01
resume: 3th insert
*************************** 4. row ***************************
id: 3
name: zhaoliu
sex: 1
birthday: 1983-04-11
salary: 8500
entry_data: 1999-12-01
resume: 4th insert
*************************** 5. row ***************************
id: 5
name: okk
sex: 1
birthday: 1983-04-11
salary: 10500
entry_data: 1999-12-01
resume: 5th insert
5 rows in set (0.00 sec)
ERROR:
No query specified

删除数据


mysql> delete from employee where name = 'wangwu';
Query OK, 1 row affected (0.00 sec)
mysql> select*from employee /G;
*************************** 1. row ***************************
id: 1
name: zhangsan
sex: 1
birthday: 1983-04-11
salary: 30600
entry_data: 1999-12-01
resume: THis is first insert
*************************** 2. row ***************************
id: 2
name: lisi
sex: 1
birthday: 1983-04-11
salary: 30500
entry_data: 1999-12-01
resume: THis is secord insert
*************************** 3. row ***************************
id: 3
name: zhaoliu
sex: 1
birthday: 1983-04-11
salary: 8500
entry_data: 1999-12-01
resume: 4th insert
*************************** 4. row ***************************
id: 5
name: okk
sex: 1
birthday: 1983-04-11
salary: 10500
entry_data: 1999-12-01
resume: 5th insert
4 rows in set (0.00 sec)
ERROR:
No query specified

注意:删除的时候一定要注意加条件,否则会删除表中所有数据。


mysql> select*from employee /G;
*************************** 1. row ***************************
id: 1
name: zhangsan
sex: 1
birthday: 1983-04-11
salary: 30600
entry_data: 1999-12-01
resume: THis is first insert
*************************** 2. row ***************************
id: 2
name: lisi
sex: 1
birthday: 1983-04-11
salary: 30500
entry_data: 1999-12-01
resume: THis is secord insert
*************************** 3. row ***************************
id: 3
name: zhaoliu
sex: 1
birthday: 1983-04-11
salary: 8500
entry_data: 1999-12-01
resume: 4th insert
*************************** 4. row ***************************
id: 5
name: okk
sex: 1
birthday: 1983-04-11
salary: 10500
entry_data: 1999-12-01
resume: 5th insert
4 rows in set (0.00 sec)
ERROR:
No query specified
mysql> delete from employee;
Query OK, 4 rows affected (0.00 sec)
mysql> select*from employee /G;
Empty set (0.00 sec)
ERROR:
No query specified

truncate方式删除表中记录


mysql> select*from employee /G;
*************************** 1. row ***************************
id: 1
name: zhangsan
sex: 1
birthday: 1983-04-11
salary: 30000
entry_data: 1999-12-01
resume: THis is first insert
*************************** 2. row ***************************
id: 2
name: lisi
sex: 1
birthday: 1983-04-11
salary: 30000
entry_data: 1999-12-01
resume: THis is secord insert
*************************** 3. row ***************************
id: 3
name: wangwu
sex: 1
birthday: 1983-04-11
salary: 9000
entry_data: 1999-12-01
resume: THis is first insert
3 rows in set (0.00 sec)
ERROR:
No query specified
mysql> truncate employee;
Query OK, 0 rows affected (0.08 sec)
注意:

delete和truncate都删除表中数据,效率上truncate比delete快,但truncate删除后不记录mysql日志,不可以恢复数据。delete的效果有点像将mysql表中所有记录一条一条删除到删完,而truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表。


三、小结:
1、数据库的增删改查指令分别是:
①查看数据库 show databases;
②修改数据库 alter database Test character set utf8;
③创建数据库 create database Test;
④删除数据库 drop database Test;
2、数据库表的增删改查指令分别是:

注意:要先使用一个数据库(use指令),然后对它执行相应操作。


①创建表 create table people(id int, name varchar(20));
②修改表
修改表名 rename table people to employee;
修改表中一个字段 alter table employee modify column height float;
删除表中一个字段 alter table employee drop column height;
③删除表 drop table employee;
④查看表结构 desc employee;
3、表中数据的增删改查
①新增一条数据:insert into employee values(1, ‘zhangsan’, 1, ‘1983-04-11’, 30000, ‘1999-12-01’,’THis is first insert’);
②删除一条数据:delete from employee where name = ‘zhangsan’;
③更新一条数据:update employee set salary = salary + 500;
④查找一条数据:select * from employee /G;

以上便是MySQL数据库的基本操作。

第七城市th7cn

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台