mysql基础(附具体操作代码)
发布时间:2020-12-12 02:15:25 所属栏目:MySql教程 来源:网络整理
导读:# 注释内容 -- 注释内容 -- 创建数据库 king CREATE DATABASE king; 查看当前服务器下有哪些数据库 SHOW DATABASES;SHOW SCHEMAS; 查看king数据库的详细信息 SHOW king; 创建数据库queen 并且指定编码方式为gbk DATABASE IF NOT EXISTS queen DEFAULT CHARAC
# 注释内容 -- 注释内容 -- 创建数据库 king CREATE DATABASE king; 查看当前服务器下有哪些数据库 SHOW DATABASES; SHOW SCHEMAS; 查看king数据库的详细信息 SHOW king; 创建数据库queen 并且指定编码方式为gbk DATABASE IF NOT EXISTS queen DEFAULT CHARACTER SET 'GBK'; 查看当前服务器下全部的数据库 SHOW DATABASES; 查看queen指定数据库的详细信息 SHOW queen; 将queen的数据库的编码方式改为utf8 ALTER DATABASE queen UTF8 打开queen的数据库 USE 得到当前打开的数据库 SELECT (); SCHEMA 删除king数据库 DROP EXISTSEXISTS queen; 创建测试用数据库 test EXISTS test ; 打开test test; 用户表 test_user TABLE test_user( id INT,username VARCHAR(20),password CHAR(3250TINYINT1811FLOAT(8,2TINYINT(1100男',女保密) )ENGINE=INNODB CHARSET=UTF8; 查看test_user表的表结构 DESC test_user; DESCRIBE test_user; SHOW COLUMNS FROM test_user; 删除test_user表 查看当前数据库下的数据表 查看test_user表的详细信息 通过三种方式查看指定数据表的表结构 最后删除test_user数据表 数据库增加数据与查询数据 INSERT [INTO] tbl_name(id,username,...) VALUES(1,'KING',...); 向test_user表插入一条记录 INSERT test_user(id,password,email,age,card,tel,salary,married,addr,sex) VALUES(1,1)">king965794175@qq.com24,1)">xxxxxxxxxxxxxxxxxx132xxxxxxxx88888.68,1); font-weight: bold">0,1)">宁波); 查询表中所有记录 SELECT * FROM tbl_name; SELECT * -5,1)">123456190,1); font-weight: bold">2345.68,1)">'); 数据类型测试-测试整型+无符号+零填充 测试整型 TABLE test_int( a tinyintsmallintintbigint ); INSERT test_int(a) 128); 如果超出数据的范围,会产生截断现象 1291270 测试无符号 test_unsigned( a UNSIGNED ); INSERT test_unsigned(a,b) 12,1); font-weight: bold">120256 测试零填充 ZEROFILL test_int1( a ZEROFILL,c mediumint ZEROFILL,1)"> ZEROFILL ); INSERT test_int1(a,b,c,d,e) test_int2( a tinyint(smallint() ); INSERT test_int2(a,1); font-weight: bold">123,1); font-weight: bold">45678); 数据类型测试-测试浮点型 测试浮点型 test_float( a 6,1)">DOUBLE(DECIMAL() ); INSERT test_float(a,c) 4.143,1); font-weight: bold">4.146,1); font-weight: bold">4.149 test_float1( a FLOATDOUBLEDECIMALINSERT test_float1(a,1); font-weight: bold">4.6491234567.89,1); font-weight: bold">2345678.9,1); font-weight: bold">567890.89); 数据类型测试-测试char和varchar+text+ENUM+set 测试char 和 varchar test_str( a 5INSERT test_str(a,1)">VALUES('',1)">''aababcabcdabcdeabcdef 123 SELECT CONCAT(*'),CONCAT(') test_str; 测试TEXT不能有默认值 test_str1( content TEXT DEFAULT THIS IS A TEST ); 测试ENUM test_enum1( sex ENUM(女 保密 INSERT test_enum(sex) 男1VALUES(NULL3 测试SET test_set( a SET(ABCDEFINSERT test_set(a) C,D,E2); 数据类型测试-测试时间类型 HH:MM:SS [D HH:MM:SS] D表示天数 0~34 测试TIME类型 test_time( a TIME ); INSERT test_time(a) 12:23:452 12:23:4522:22222 22 HHMMSS 1212120787878VALUES(NOW()); CURRENT_TIME 测试DATE类型 YYYY-MM-DD YYYYMMDD test_date( a DATE ); INSERT test_date(a) 2017-03-042017-2-134007-03-23400712124007@12@124008#13#134009.8.14); YY-MM-DD YYMMDD 70~99 转换成1970~1999 00~69 2000~2069 780902650902880902CURRENT_DATE 测试DATETIME test_datetime( a DATETIMEINSERT test_datetime(a) 1004-09-12 13:24:56720305121212 测试TIMESTAMP test_timestamp( a TIMESTAMPINSERT test_timestamp(a) 1978-10-23 12:12:12 插入CURRENT_TIMESTAMP INSERT test_timestamp CURRENT_TIMESTAMP 插入NULL 什么也不写 得到当前系统日期和时间 (); 测试YEAR test_year( a YEAR ); INSERT test_year(a) 1901 00~69 2000~2069 70~99 1970~1999 0 插入的结果是0000 '0' 插入的结果是2000 数据类型测试-测试主键+复合主键+自增 测试主键 test_primary_key( id INT UNSIGNED PRIMARY KEYINSERT test_primary_key(id,username) king123INSERT test_primary_key(username) QUEEN test_primary_key1( id test_primary_key2( id UNSIGNED,(id) ); CREATE TABLE test_primary_key3( id INT UNSIGNED PRIMARY KEY,1)"> courseId INT UNSIGNED PRIMARY KEY,1)"> username VARCHAR(20),1)"> email VARCHAR(50) ); 复合主键 test_primary_key3( id (id,courseId) ); 1-a test_primary_key3(id,courseId,email) b2,1)">1king 测试AUTO_INCREMENT test_auto_increment( id AUTO_INCREMENT,1)">INSERT test_auto_increment(username) INSERT test_auto_increment(id,1)">NULL,1)">DEFAULT,1); font-weight: bold">15,1)">G'); 数据类型测试-测试非空+默认值+唯一键 测试非空 test_not_null( a varchar(20) not nullINSERT test_not_null(a,INSERT test_not_null(a) TEST 测试默认值 test_default( id INT UNSIGNED AUTO_INCREMENT NOT TINYINT UNSIGNED DEFAULT 50) NULL INSERT test_default(username) INSERT test_default(username,email) 30,1)">DEFAULT test_default1( id c') 测试UNIQUE KEY test_unique( id UNIQUE 50) UNIQUE18) INSERT test_unique(username,card) A@QQ.COM11A@QQ.COM12NULL); 数据类型测试-测试布尔类型 用户表test_user `test_user`( `id` KEY COMMENT 用户编号UNIQUE COMMENT 用户名32) NULL COMMENT 密码邮箱TINYINT UNSIGNED 18 COMMENT 年龄' COMMENT 性别11) 电话北京地址18) 身份证号1) 0 COMMENT 0代表未结婚,1代表已结婚2) 薪水 )ENGINE=INNODB DEFAULT CHARSET 测试布尔类型 test_bool( id test_bool1( id 测试添加和删除字段 user1( id 添加用户名字段 username VARCHAR(20) user1 ADD username 添加密码字段 password CHAR(32) NOT NULL ADD password 添加邮箱字段 email VARCHAR(50) NOT NULL UNIQUE 加到username之后 ADD email AFTER username; 添加测试字段 test TINYINT(1) NOT NULL DEFAULT 0; 加到首位 ADD test FIRST; 删除test字段 DROP test; 添加age、addr字段,删除email字段 ADD age ADD addr 100) email; 创建数据表 id / proName 添加price FLOAT(8,2) UNSIGNED NOT NULL DEFAULT 0 添加num INT UNSIGNED NOT NULL DEFAULT 100 添加测试字段test VARCHAR(50) NOT NULL 加到首位 添加测试字段test1 CHAR(23) NOT NULL 添加到price之后 删除test字段 选中一次数据表完成多次操作,添加一个desc TEXT,添加isSale 布尔类型 0下架 1代表在卖 删除test1字段 测试添加删除默认值操作 user2( id 给email字段添加默认值 test@qq.com user2 ALTER email SET test@qq.com 删除age字段的默认值 ALTER age 添加地址字段 给地址字段添加默认值为北京 删除商品数量的默认值 测试修改字段类型和字段属性、字段名称 user3( id 5) 10) 将用户名字段的类型改为20 user3 MODIFY username 将密码的长度改为40 user3 MODIFY password 40) 将email字段改为VARCHAR(50) NOT NULL FIRST user3 MODIFY email 将username 名称改为user user3 CHANGE username user 将password 名称改为pwd user3 CHANGE password pwd 将email改成userEmail 类型改成VARCHAR(100) DEFAULT 'test@qq.com' user3 CHANGE email userEmail 100) ; 测试添加和删除主键 user4( id 添加主键 user4 ADD (id); 删除主键 user5( id 将id的AUTO_INCREMENT去掉 user5 MODIFY id UNSIGNED; 测试添加和删除唯一 user6( id 删除唯一索引 username 和email user6 INDEX username; 添加唯一索引 (username); uni_email(email); 将user6改为user666 user6 RENAME TO user666; user666 RENAME AS user6; user6 RENAME user666; RENAME TABLE user666 TO user6; 存储引擎 测试MEMORY存储引擎 m1( a MEMORY; 测试CSV存储引擎 c1( a CSV; INSERT c1(a,1)">AAABBBCCCDDD 测试ARCHIVE存储引擎 TABLE a1 AS INFORMATION_SCHEMA.columns; INSERT INTO a1 a1; 11026432 57177 TABLE a2 ENGINE=ARCHIVE 742354 55392 测试MyISAM存储引擎 test_myisam( a MyISAM; myisam_1( a char(30MyISAM; myisam_2( a myisam_3( a =MyISAM ROW_FORMATFIXED; test_innodb( id ); 添加数据 测试添加记录 EXISTS king EXISTS user( id 编号 不指定字段名称 INSERT user VALUE(queen25,1)">queen@qq.comlily26,1)">lily@qq.com 列出指定字段的形式 user(username,1)">roserose@qq.comuser(age,id,1); font-weight: bold">34,1)">test 一次插入3条记录 56,1)">b@qq.com14,1)">c@qq.com INSERT ...SET 的形式 SET username=d=45,emaild@qq.com INSERT SELECT user(username) SELECT a FROM test; 修改删除数据 测试更新语句 修改第一个用户的信息 id=1 UPDATE SET age29 WHERE id 修改id=3的用户,username age email 47,1)">lilys@qq.comlilys' 所有用户年龄+10 =age+10 将id<=5的用户年龄改为-20,将邮箱改为默认值 20,1)"><= 测试删除语句 删除用户名为king DELETE FROM WHERE username 删除年龄为24的用户 WHERE age24 删除表中所有记录 '); 查询操作 测试查询操作 user1( id 18 COMMENT 0代表未结婚,1代表已婚UTF8; INSERT user1 23,1); font-weight: bold">50000INSERT user1(username,sex,salary) 27,1)">上海2500031,sex40000张三38,1); font-weight: bold">15000张三风张子怡39,1); font-weight: bold">85000汪峰42,1)">深圳95000刘德华58,1)">广州115000吴亦凡28,1); font-weight: bold">75000奶茶妹18,1); font-weight: bold">65000刘嘉玲36,1)"> 查询表中所有记录 user1; username,age SELECT username,age 查询king数据库下user1表中的所有记录 king.user1; 查询user1表中的id 编号 username 用户名 sex 性别 SELECT id AS ' SELECT id age,age id,1)"> 给表起别名 SELECT id,1)">FROM user1 u; 测试表名.字段名 SELECT user1.id,user1.username,user1.age user1 ; SELECT u.id,u.username,u.addr,u.sex 测试WHERE 条件的比较运算符 查询id,age id=5的用户 添加desc字段 VARCHAR(100) ADD userDesc 更新id<=9的用户 userDesc='this is a test' UPDATE user1 SET userDescthis is a test9 查询用户userDesc 为NULL的用户 WHERE userDesc 检测NULL值 <=> IS [NOT] NULL检测NULL值 WHERE userDesc IS 测试范围BETWEEN AND 查询年龄在18~30之间的用户 WHERE age BETWEEN 18 AND 查询薪水在10000~50000之间的用户 WHERE salary 10000 测试指定集合 IN 查询编号为1,3,5,7,9 WHERE id IN(3,1); font-weight: bold">7,1); font-weight: bold">9,1); font-weight: bold">29,1); font-weight: bold">78WHERE username IN( 测试逻辑运算符 查询性别为男并且年龄>=20的用户 WHERE sex' AND age>= id>=5 && age<=30 5 30 AND sex 要求sex='女' 并且 addr='宁波' AND addr 查询薪水范围在60000~10000并且性别为男 addr='宁波' 60000 100000 查询id=1 或者 用户名为queen 1 OR username 测试模糊查询 LIKE 要求用户名中包含三 %三% 用户名中包含n %in% 要求查询出姓张的用户 张% 查询以风结尾的用户 %风 用户名长度为三位的用户 ___张_张_% 测试分组 按照性别分组sex GROUP BY sex; 按照addr分组 addr; 按照性别分组,查询组中的用户名有哪些 SELECT GROUP_CONCAT(username),GROUP_CONCAT(addr) 测试COUNT() SELECT COUNT(*) COUNT(id) 按照sex分组,得到用户名详情,并且分别组中的总人数 SELECT sex,GROUP_CONCAT(username) AS usersDetail,1)">AS totalUsers 按照addr分组,得到用户名的详情,总人数,得到组中年龄的总和,年龄的最大值、最小值、平均值和 SELECT addr,GROUP_CONCAT(username) usersDetail, totalUsers,1)">SUM(age) sum_age,1)">MAX(age) max_age,1)">MIN(age) min_age,1)">AVG(age) avg_age 按照sex分组,统计组中总人数、用户名详情,得到薪水总和,薪水最大值、最小值、平均值 sex,1)">SUM(salary) sum_salary,1)">MAX(salary) max_salary,1)">MIN(salary) min_salary,1)">AVG(salary) avg_salary SELECT GROUP_CONCAT(username) totalUsers sex WITH ROLLUP; 按照字段的位置来分组 id,1)">BY 查询age>=30的用户并且按照sex分组 30 按照addr分组,统计总人数 对于分组结果进行二次筛选,条件是组中总人数>=3 addr HAVING *)HAVING totalUsers 按照addr分组, 要求平均薪水>=40000 HAVING avg_salary 测试排序 按照id降序排列 ORDER BY id 按照age升序 age ; 按照多个字段排序 BY age ASC,id ASC 测试条件+排序 实现随机记录 BY RAND(); 测试LIMIT语句 显示结果集的前5条记录 user1 LIMIT 显示前3条记录 更新前3条记录,将age+5 5 LIMIT 按照id降序排列,更新前三条记录,将age-10 10 DESC LIMIT 删除前三条记录 LIMIT 测试完整SELECT 语句的形式 2 BY totalUsers 2; 多表查询 EXISTS test2 test2; emp( id 部门对应的编号INSERT emp(username,depId) john测试用户6 dep( id TINYINT UNSIGNED AUTO_INCREMENT INSERT dep(depName,depDesc) PHP教学部研发PHP课件JAVA教学部研发JAVA课件WEB前端教学部研发WEB前端课件IOS教学部研发IOS课件 查询 emp id username age 部门名称 dep depName SELECT emp.id,emp.username,emp.age,dep.depName emp,dep; e.id,e.username,e.age,d.depName FROM emp e INNER JOIN dep d ON e.depIdd.id; 查询emp id username age addr dep id depName depDesc FROM dep d JOIN emp ON d.ide.depId; 测试左外连接 e LEFT OUTER 测试右外连接 RIGHT =d.id; 实例 创建管理员表 TABLE ( id 382771946@qq.com哈尔滨ef重启 创建省份表 provinces( id INSERT provinces(proName) -- g 查询user id,username provinces proName u.id,p.proName u JOIN provinces p ON u.proIdp.id; 管理员admin id username email proId admin( id INSERT admin(username,1)"> 商品分类cate id cateName cateDesc cate( id INSERT cate(cateName) 母婴服装电子 商品表products id productName,price,cateId products( id products(productName,cateId,adminId) iphone99888,1)">adidas388,1)">nike888,1)">奶瓶288,1)"> 查询products id productName price --- cate cateName p.id,p.productName,p.price,c.cateName FROM products p JOIN cate c ON p.cateIdc.id; 查询管理员 id username email -- provinces proName a.id,a.username,a.email,1)">FROM admin a ON a.proIdp.id; 查询 products id productName price cate cateName admin username email JOIN admin a ON p.adminIda.id c.id WHERE p.price<1000 BY p.price products id productName price admin username email provinces proName 测试外键 测试外键 新闻分类表 news_cate news_cate( id 新闻表 news news( id 1000) INSERT news_cate(cateName) 国内新闻国际新闻娱乐新闻体育新闻INSERT news(title,content,cateId) a1aaaa1a2aaaa2a3aaaa34a4aaaa4a5aaaa5 查询news id title content news_cate cateName n.id,n.title,n.content,1)">FROM news n JOIN news_cate ON n.cateIda6aaaa645 添加外键 INNODB; FOREIGN KEY(cateId) REFERENCES news_cate(id) )ENGINE 测试非法记录 b1bbbb18 测试删除父表中的记录 和删除父表 FROM news_cate UPDATE news_cate SET id教育新闻 将教育新闻 改成教育 SET cateName教育50 WHERE cateName 添加外键名称 CONSTRAINT cateId_fk_newsCate 删除外键 news cateId_fk_newsCate; 添加外键 news_cate(id); news_ibfk_1; 指定级联操作 DELETE CASCADE UPDATE CASCADE news_cate(id) ON CASCADE UPDATE CASCADE; 测试子查询 测试子查询 测试由IN引发的子查询 emp WHERE depId IN ( dep); 学员stu stu( id INSERT stu(username,score) 9575zhangsan69lisiwangwu87zhaoliu88tianqi98ceshi99tiancai 分数级别level leveltinyint UNSIGNED AUTO_INCREMENT TINYINT UNSIGNED COMMENT 分数level(score) 90),(80),1); font-weight: bold">70 查询出成绩优秀的学员 SELECT score FROM level stu WHERE score>=( 查询出没有得到评级的学员 <=( 由EXISTS 引发的子查询 WHERE EXISTS (SELECT depName 带有ANY SOME ALL关键字的子查询 >= ANY(SOME(ALL(< =ANY(!=ALL( 创建一个user1表,id username int UNSIGNED AUTO_INCREMENT ) ) emp; 将user表中的用户名写入到user1表中 INSERT user1(username) SELECT username 将stu表中的tiancai用户名添加到user2表中 INSERT user2 =(FROM stu 去掉字段的重复值 DISTINCT(username) user2; 将user1和user2数据合并到一起 UNION FROM user2; 测试自身连接 测试自身连接 cate( id SMALLINT UNSIGNED AUTO_INCREMENT SMALLINT UNSIGNED INSERT cate(cateName,pId) 数码箱包男装女装内衣电视冰箱洗衣机爱马仕LVGUCCI夹克衬衫裤子液晶电视等离子电视背投电视 查询所有的分类信息,并且得到其父分类 SELECT s.id,s.cateName AS sCateName,p.cateName pCateName FROM cate s ON s.pId 查询所有的分类及其子分类 SELECT p.id,1)">AS pCateName,1)"> sCateName 查询所有的分类并且得到子分类的数目 COUNT(s.cateName) count p.id p.cateName ; sCate 表 id | cateName | pId | +--+-----------------+-----+ | | 服装 | 0 2 | 数码 3 | 玩具 4 | 男装 | 女装 6 | 内衣 | 10 | 电视 11 | 冰箱 12 | 洗衣机 13 | 爱马仕 14 | LV 15 | GUCCI 16 | 夹克 17 | 衬衫 | 裤子 19 | 液晶电视 20 | 等离子电视 21 | 背投电视 pCate id 10 测试字符串函数 测试字符串函数 CHAR_LENGTH():得到字符串的字符数 SELECT CHAR_LENGTH( LENGTH():返回字符串的长度 SELECT LENGTH( CONCAT(s1,s2,....):将字符串合并成一个字符串 如果字符串中包含NULL,返回拼接结果就是NULL CONCAT_WS(x,s1,s2....):以指定分隔符拼接字符串 SELECT CONCAT_WS(- 如果null在拼接的内容中,则转化成空字符串 如果分隔符为null,拼接的结果为null SELECT CONCAT_WS(null,1)"> 将字符串转换成大写或者小写 UPPER()| UCASE() LOWER()|LCASE() UPPER(hello kinghello imoocLOWER(HELLO ADMINHELLO EVERYBODY 字符串的反转REVERSE() REVERSE( LEFT()|RIGHT():返回字符串的前几个字符或者后几个字符 LEFT(hello2),1)">RIGHT( LPAD()|RPAD():用字符串填充到指定长度 SELECT LPAD(10,1)">?SELECT RPAD(! 去掉字符串两端的空格TRIM()|LTRIM()|RTRIM(): abc LTRIM(RTRIM( REPEAT():重复指定的次数 SELECT REPEAT( REPLACE():字符串 REPLACE( 截取字符串SUBSTRING SUBSTRING( 比较字符串 SELECT STRCMP('); 测试日期时间函数 测试日期时间函数 返回当前日期 SELECT CURDATE(),1)"> 返回当前时间 SELECT CURTIME(),1)"> 返回当前的日期时间 SELECT NOW(),1)">(),SYSDATE(); 返回日期中的月份和月份的名称 MONTH(2017-02-19MONTH(CURDATE()),MONTHNAME(CURDATE()); 返回星期几 DAYNAME(NOW()); 返回一周内的第几天,0代表星期一 DAYOFWEEK(NOW()); WEEK(NOW()); YEAR(NOW()),1)">MONTH(NOW()),1)">DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()); DATEDIFF()计算两个日期相差的天数 DATEDIFF(2019-03-062019-03-02'); 其他函数测试 测试其它常用函数 VERSION(),CONNECTION_ID(); USER(),1)">CURRENT_USER(),1)">SYSTEM_USER(),1)">SESSION_USER 得到上一步插入操作产生AUTO_INCREMENT的值 LAST_INSERT_ID(); SELECT MD5( PASSWORD():密码加密算法 SELECT PASSWORD(root'); ? (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |