MySQL常用语句

MySQL常用语句

连接远程主机MySQL： mysql -h主机IP -u root -p密码
创建数据库： CREATE DATABASE 数据库名;(information_schema mysql performance_schema sys四个为系统数据库）
删除数据库： DROP DATABASE 数据库名；
使用数据库： USE 数据库名；（use Secondhand）
创建表： CREATE TABLE IF NOT EXISTS user(
user_id INT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(20) NOT NULL,
user_college VARCHAR(20) NOT NULL,
user_major VARCHAR(20) NOT NULL,
user_studentid CHAR(10) NOT NULL,
PRIMARY KEY ( user_id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
查看所有表： SHOW TABLIES;
查看某个表： DESC USERS;
删除表： DROP TABLE 数据表;
插入数据： INSERT INTO USER
(user_id,user_name,user_college,user_major,user_studentid)
VALUES
(“00201615461”,“肖克”,“管理学院”,“信息管理与信息系统”,“U201615461”);
查询： select * from user;
select user_name,user_major //列名
from user
where user_name=“肖克” //条件“=”判断相等
limit 1; //限制返回数
更新： UPDATE user SET user_id=1201615461 WHERE user_name=“肖克”;
删除： DELETE FROM user WHERE user_id=3;

LIKE语句： SELECT * FROM user WHERE user_name LIKE ‘肖%’;（模糊查询）

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
SELECT user_name FROM user
-> UNION
-> SELECT user_name FROM user;
输出结果只有一条：肖克

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）
SELECT user_name FROM user
-> UNION ALL
-> SELECT user_name FROM user;
输出结果有两条：肖克 肖克

排序： SELECT * FROM user ORDER BY user_id ASC;（升序）
SELECT * FROM user ORDER BY user_id DESC;（降序）

分组：select user_name,COUNT() from user group by user_name;（进行分组统计，比如所有消费数据中，每人的消费次数）
后面加having作为分组的条件，如having COUNT()>1
分组显示和总计：SELECT coalesce(user_name, ‘总数’), SUM(user_id) as sum_id FROM user GROUP BY user_name WITH ROLLUP;
coalesce（a,b,c）

内连接：SELECT a.user_id,a.user_name,b.user_account FROM user a INNER JOIN account b ON a.user_id = b.usre_id;
INNER JOIN 可改为 LEFT JOIN 或 RIGHT JOIN

NULL值操作：IS NULL 或 IS NOT NULL 或<=>（两边都是null） 不可使用“=NULL”

正则表达式（REGEXP）： SELECT user_name FROM user WHERE user_name REGEXP ‘^肖’;（查找以“肖”开头的）
SELECT name FROM person_tbl WHERE name REGEXP ‘ok${}^{\prime };（查找以“ok”结尾的）SELECTnameFROMperso{n}_{t}blWHEREnameREGEX{P}^{\prime }ma{r}^{\prime };（查找包含“mar”的）SELECTnameFROMperso{n}_{t}blWHEREnameREGEX{P}^{\prime [}aeiou]|ok$’;（查找以元音字母开头的或以“ok”结尾的）

事务：begin（开始）,commit（提交）,rollback（回滚）

ALTER 命令，修改数据表名或字段：
删除表中字段： ALTER TABLE testalter_tbl DROP i;
添加字段： ALTER TABLE testalter_tbl ADD i INT;
修改表中字段： ALTER TABLE testalter_tbl MODIFY c CHAR(10);（修改c为CHAR(10)）
后面可以添加：NOT NULL DEFAULT 100（不空，默认100）
修改表中字段： ALTER TABLE testalter_tbl CHANGE i j BIGINT;（修改字段i为j，类型变为BIGINT）
修改字段默认值： ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;（修改i的默认值为1000）
删除字段默认值： ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
添加主键： ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
删除主键： ALTER TABLE testalter_tbl DROP PRIMARY KEY;
修改表名： ALTER TABLE testalter_tbl RENAME TO alter_tbl;

索引操作：
创建索引： CREATE INDEX indexName ON user(user_name(20));
添加索引： ALTER table tableName ADD INDEX indexName(columnName);
建表时直接指定索引：
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
删除索引： DROP INDEX [indexName] ON mytable; 如：drop index indexName on user;
删除索引： ALTER TABLE testalter_tbl DROP INDEX c;
创建唯一索引： CREATE UNIQUE INDEX indexName ON mytable(username(length));
添加唯一索引： ALTER table mytable ADD UNIQUE [indexName] (username(length));
建表时指定唯一索引：
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
添加全文索引： ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);
显示索引信息：SHOW INDEX FROM table_name;

复制表：
INSERT INTO clone_tbl (runoob_id,
-> runoob_title,
-> runoob_author,
-> submission_date)
-> SELECT runoob_id,runoob_title,
-> runoob_author,submission_date
-> FROM runoob_tbl;

序列：自动增加 AUTO_INCREMENT
创建表时：id INT UNSIGNED NOT NULL AUTO_INCREMENT,
改变自动增长的开始值：ALTER TABLE t AUTO_INCREMENT = 100;

读取不重复数据DISTINCT： SELECT DISTINCT last_name, first_name FROM person_tbl;
删除重复数据： 可以通过添加主键和唯一索引

导出数据：show variables like ‘%secure%’;先找到 secure-file-priv 当前的位置
本机在C:\ProgramData\MySQL\MySQL Server 8.0\Uploads
进入，在C:\ProgramData\MySQL\MySQL Server 8.0\my文件中将这个路径改为F:\data
SELECT * FROM user INTO OUTFILE ‘F:\data\user.txt’;
最好的格式：
SELECT a,b,a+b INTO OUTFILE ‘/tmp/result.text’
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\n’
FROM test_table;

导入数据：
-u用户名 -p密码 < 要导入的数据库数据(runoob.sql)
-uroot -p2271787566 < runoob.sql
source命令导入： source /home/abc/abc.sql
LOAD DATA LOCAL INFILE ‘dump.txt’ INTO TABLE mytbl;