mysql runoob_mysql的基本知识总结

[root@host]# mysql -u root -p

Enter password:***** 连接mysql

mysql> create DATABASE RUNOOB; 创建数据库

mysql> drop database RUNOOB; 删除数据库

mysql> use RUNOOB; 选择数据库

mysql> CREATE TABLE runoob_tbl(

-> runoob_id INT NOT NULL AUTO_INCREMENT,

-> runoob_title VARCHAR(100) NOT NULL,

-> runoob_author VARCHAR(40) NOT NULL,

-> submission_date DATE,

-> PRIMARY KEY ( runoob_id )

-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.16 sec)

mysql> 创建数据表

mysql> DROP TABLE runoob_tbl 删除数据表

mysql> INSERT INTO runoob_tbl

-> (runoob_title, runoob_author, submission_date)

-> VALUES

-> ("JAVA 教程", "http://RUNOOB.COM", '2016-05-06'); 插入数据

select * from runoob_tbl; 读取数据表

SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程'; 条件筛选

mysql> UPDATErunoob_tblSETrunoob_title='学习 C++'WHERE runoob_id=3 修改数据

mysql> DELETE FROM runoob_tbl WHERE runoob_id=3; 删除数据

SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM' 匹配以COM结尾的数据

SELECT country FROM Websites

UNION

SELECT country FROM apps

ORDER BY country; "Websites" 和 "apps" 表中选取所有不同的country(只有不同的值)

SELECT country FROM Websites

UNION ALL

SELECT country FROM apps

ORDER BY country; "Websites" 和 "apps" 表中选取所有的country(也有重复的值

SELECT country, name FROM Websites

WHERE country='CN'

UNION ALL

SELECT country, app_name FROM apps

WHERE country='CN'

ORDER BY country; "Websites" 和 "apps" 表中选取所有的中国(CN)的数据(也有重复的值)

mysql> SELECT * fromrunoob_tblORDERBYsubmission_dateASC; 日期升序排列

mysql> SELECT * fromrunoob_tblORDERBYsubmission_dateDESC;日期降序排列

mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; 计数名字次数

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP; 计数累加singin的大小

mysql> SELECTa.runoob_id, a.runoob_author, b.runoob_countFROMrunoob_tblaINNERJOINtcount_tblbONa.runoob_author = b.runoob_author; 相同的作者将两表拼接(交集)

mysql> SELECTa.runoob_id, a.runoob_author, b.runoob_countFROMrunoob_tblaLEFTJOINtcount_tblbONa.runoob_author = b.runoob_author; 将右表的值给左表，输出左表

mysql> SELECTa.runoob_id, a.runoob_author, b.runoob_countFROMrunoob_tblaRIGHTJOINtcount_tblbONa.runoob_author = b.runoob_author; 将左表的值给右表，输出右表

mysql> SELECT * FROMrunoob_test_tbl WHERE runoob_count IS NULL;

mysql> SELECT * fromrunoob_test_tbl WHERE runoob_count IS NOT NULL;

判断是否为null不能用=要用IS

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

'st'为开头的所有数据

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

'ok'为结尾的所有数据

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

包含'mar'字符串的所有数据

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

'aeiou' 开头 或 以'ok'字符串结尾的所有数据

mysql> begin; 开始事务

语句

mysql> rollback; 回滚 (回滚到开始事务前)

mysql> ALTER TABLE testalter_tbl DROP i; 删除i变量

mysql> ALTER TABLE testalter_tbl ADD i INT; 添加i变量

ALTER TABLE testalter_tbl ADD i INT FIRST; 添加i变量为第一个

ALTER TABLE testalter_tbl ADD i INT AFTER c 添加i变量到c之后的位置

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10); 修改c变量类型为char(10)

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT; 修改i变量为j 类型为bigint

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; 修改i的默认值为100

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; 删除i的默认值

mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM; 修改表的类型

mysql> SHOW TABLE STATUS LIKE 'testalter_tbl' 显示表的类型

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl; 修改表的名称

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt 把表导出

mysql> LOAD DATA LOCAL INFILE 'dump.txt'

-> INTO TABLE mytbl (b, c, a); 把表导入