目录
- 安装mysql服务器
- 查看系统版本
- 开始安装mysql
- 查看是否安装成功
- 启动mysql
- mysql学习
- DDL 定义语言 (CREATE/DROP/ALTER)
- DML 操作语言(INSERT/UPDATE/DELETE)
- DQL 查询语言(SELECT)
- DCL 控制语言(GRANT/REVOKE/COMMIT/ROLLBACK)
1.安装mysql服务器
1.1 查看系统版本yan@yan:~$ cat /etc/issueUbuntu 16.10 \n \lyan@yan:~$ cat /etc/issue.net Ubuntu 16.10yan@yan:~$ uname -aLinux yan 4.8.0-22-generic #24-Ubuntu SMP Sat Oct 8 09:14:42 UTC 2016 i686 i686 i686 GNU/Linuxyan@yan:~$
1.2 开始安装mysql
root@yan:~# apt-get install mysql-serverroot@yan:~# apt-get install mysql-clientroot@yan:~# apt-get install libmysqlclient-dev
注意：安装software时，一定要是root用户登录哟，否则会报错(没有权限)
1.3 查看是否安装成功
root@yan:~# [ `ps aux | grep mysql | grep -v "grep" | wc -l` -ge 0 ] && echo -e "Install OK" || echo -e "Install fail"
如果提示Install OK 则表示有进程 如果提示Install fail 则可能需要自己重新启动一下程序了
重启命令：root@yan:~# /etc/init.d/mysql restart
1.4 启动mysql
root@yan:~# mysql -uroot -p
其中:mysql 代表mysql程序 -u代表USER -p 代表password
综合起来就是：使用root用户(mysql用户和系统用户是两码事)来登录mysql并且有密码
2.mysql学习
2.1 DDL 定义语言 (主要讲三个:CREATE/DROP/ALTER)
新建数据库(新建了一个数据库，其名称为yan)mysql> CREATE DATABASE yan;Query OK, 1 row affected (0.00 sec)
查看数据库(可见，yan已经创建成功了)
mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || yan |+--------------------+5 rows in set (0.00 sec)
查看建库语句
mysql> SHOW CREATE DATABASE yan;+----------+----------------------------------------------------------------+| Database | Create Database |+----------+----------------------------------------------------------------+| yan | CREATE DATABASE `yan` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+----------------------------------------------------------------+1 row in set (0.00 sec)mysql>
删除数据库
mysql> DROP DATABASE yan;Query OK, 0 rows affected (0.00 sec)
再次检查数据库(可见，yan这个数据库已经没有了)
新建数据表
需求： 新建一个购物清单表
新建数据库yan
mysql> CREATE DATABASE yan;Query OK, 1 row affected (0.00 sec)
设置为默认数据库
mysql> USE yan;Database changed
新建表shopping_info
mysql> CREATE TABLE shopping_info (-> id INT(11) AUTO_INCREMENT,-> invoice INT(30) NOT NULL UNIQUE,-> name VARCHAR(50) NOT NULL,-> price FLOAT NOT NULL,-> PRIMARY KEY(id)-> );Query OK, 0 rows affected (0.33 sec)
再新建一个收据表
新建表receipt_info
mysql> CREATE TABLE receipt_info (-> id INT(11) AUTO_INCREMENT,-> date date NOT NULL,-> CONSTRAINT fk FOREIGN KEY(id) REFERENCES shopping_info(id)-> );Query OK, 0 rows affected (0.97 sec)mysql>
再新建一个数据表
、
mysql> CREATE TABLE user_info (-> id VARCHAR(50) UNIQUE NOT NULL,-> name VARCHAR(50) NOT NULL,-> price FLOAT NOT NULL DEFAULT 0,-> age INT(10) NOT NULL DEFAULT 0-> );Query OK, 0 rows affected (0.34 sec)mysql>
ALTER
现在将表user_info更改为user_infomationmysql> ALTER TABLE user_info RENAME user_information;Query OK, 0 rows affected (0.14 sec)
修改数据类型，先将price中的FLOAT数据类型修改为DOUBLE类型
mysql> ALTER TABLE user_information MODIFY price DOUBLE;Query OK, 0 rows affected (0.74 sec)Records: 0 Duplicates: 0 Warnings: 0mysql>
添加字段English_name至表user_info，位于最前面
mysql> ALTER TABLE user_information ADD english_name VARCHAR(20) NOT NULL FIRST;Query OK, 0 rows affected (0.49 sec)Records: 0 Duplicates: 0 Warnings: 0
添加字段English_name1至表user_info，位于name后面
mysql> ALTER TABLE user_information ADD english_name_1 VARCHAR(20) NOT NULL AFTER name;Query OK, 0 rows affected (0.62 sec)Records: 0 Duplicates: 0 Warnings: 0
删除字段 现在将english_name_1这个字段给删除掉
mysql> ALTER TABLE user_information DROP english_name_1;Query OK, 0 rows affected (0.48 sec)Records: 0 Duplicates: 0 Warnings: 0
修改字段排序,现在将age这个字段给提到第一
mysql> ALTER TABLE user_information MODIFY age INT(10) NOT NULL DEFAULT 0 FIRST;Query OK, 0 rows affected (0.44 sec)Records: 0 Duplicates: 0 Warnings: 0
删除receipt_info的外键约束
mysql> ALTER TABLE receipt_info DROP FOREIGN KEY fk;Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0
删除数据表shopping_info
mysql> DROP TABLE shopping_info;Query OK, 0 rows affected (0.21 sec)
2.2 DML 操作语言(INSERT/UPDATE/DELETE)
INSERT: 1.向user_information这个表插入数据mysql> INSERT INTO user_information VALUES (21,'Ming',112,'ming',12.5);Query OK, 1 row affected (0.04 sec)
2.插入部门内容
mysql> INSERT INTO user_information (id,age,english_name,name) VALUES (113,22,'kun','Skun');Query OK, 1 row affected (0.04 sec)
3.利用单引号来转意
mysql> INSERT INTO user_information VALUES (13,'xuzhneg''s',123345,'zheng''s',123.5);Query OK, 1 row affected (0.05 sec)
UPDATE:
1.修改user_information中age=21的user_information的name值，修改为Limysql> UPDATE user_information-> SET name='li'-> WHERE age=21;Query OK, 1 row affected (0.09 sec)Rows matched: 1 Changed: 1 Warnings: 0
2.多表更新操作
现向表receipt_info插入信息mysql> INSERT INTO receipt_info VALUES (112,'2017-3-27');Query OK, 1 row affected (0.04 sec)
多表更新，设置receipt.id的值，其中条件为receipt_info.id等于user_information.id的值
mysql> UPDATE receipt_info,user_information-> SET user_information.age=100-> WHERE receipt_info.id=user_information.id;Query OK, 1 row affected (0.05 sec)Rows matched: 1 Changed: 1 Warnings: 0
DELETE:
1.删除表receipt中id为112的数据mysql> DELETE FROM receipt_info WHERE id=112;Query OK, 1 row affected (0.04 sec)
2.多表删除数据
现向表receipt_info插入信息mysql> INSERT INTO receipt_info VALUES (112,'2017-3-27');Query OK, 1 row affected (0.04 sec)
删除user_information和receipt_info表中id相等的字段
mysql> DELETE receipt_info,user_information FROM user_informatiipt_info on,receWHERE user_information.ipt_info.id;Query OK, 2 rows affected (0.04 sec)
2.3 DQL 查询语言(SELECT)
1.简单查询、查询user_information表中所有数据mysql> SELECT * FROM user_information;+-----+--------------+--------+---------+-------+| age | english_name | id | name | price |+-----+--------------+--------+---------+-------+| 22 | kun | 113 | Skun | NULL || 13 | xuzhneg's | 123345 | zheng's | 123.5 |+-----+--------------+--------+---------+-------+2 rows in set (0.00 sec)
2.根据条件查询、查询user_information表中age等于22的数据
mysql> SELECT * FROM user_information WHERE age=22;+-----+--------------+-----+------+-------+| age | english_name | id | name | price |+-----+--------------+-----+------+-------+| 22 | kun | 113 | Skun | NULL |+-----+--------------+-----+------+-------+1 row in set (0.00 sec)
3.条件查询user_information表中price为NULL值的数据
mysql> SELECT * FROM user_information WHERE price is NULL;+-----+--------------+-----+------+-------+| age | english_name | id | name | price |+-----+--------------+-----+------+-------+| 22 | kun | 113 | Skun | NULL |+-----+--------------+-----+------+-------+1 row in set (0.00 sec)
4.查询user_information中的数据，并且对age进行排序
mysql> SELECT * FROM user_information ORDER BY age;+-----+--------------+--------+---------+-------+| age | english_name | id | name | price |+-----+--------------+--------+---------+-------+| 13 | xuzhneg's | 123345 | zheng's | 123.5 || 22 | kun | 113 | Skun | NULL |+-----+--------------+--------+---------+-------+2 rows in set (0.00 sec)
5.查询user_information中的数据，并且对age进行排序，且只输入1行
mysql> SELECT * FROM user_information ORDER BY age LIMIT 1;+-----+--------------+--------+---------+-------+| age | english_name | id | name | price |+-----+--------------+--------+---------+-------+| 13 | xuzhneg's | 123345 | zheng's | 123.5 |+-----+--------------+--------+---------+-------+1 row in set (0.00 sec)
6.模糊匹配、查询user_information中的数据且Name是以z开头的字符串
mysql> SELECT * FROM user_information WHERE name like 'z%';+-----+--------------+--------+---------+-------+| age | english_name | id | name | price |+-----+--------------+--------+---------+-------+| 13 | xuzhneg's | 123345 | zheng's | 123.5 |+-----+--------------+--------+---------+-------+1 row in set (0.00 sec)
7.模糊匹配、匹配user_information中的id含1的信息
mysql> SELECT * FROM user_information WHERE id like '%1%';+-----+--------------+--------+---------+-------+| age | english_name | id | name | price |+-----+--------------+--------+---------+-------+| 22 | kun | 113 | Skun | NULL || 13 | xuzhneg's | 123345 | zheng's | 123.5 |+-----+--------------+--------+---------+-------+2 rows in set (0.00 sec)
2.4 DCL 控制语言(GRANT/REVOKE/COMMIT/ROLLBACK)