NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here.
Enter current password for root (enter for none): ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Enter current password for root (enter for none): ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Enter current password for root (enter for none): OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation.
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] n ... skipping.
By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.
Remove anonymous users? [Y/n] y ... Success!
Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n ... skipping.
By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.
Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success!
Reloading the privilege tables will ensure that all changes made so far will take effect immediately. 第一次啟動(dòng)MySQL只能使用root用戶
show databases; #查看數(shù)據(jù)庫(kù) use XXX; #進(jìn)入數(shù)據(jù)庫(kù) show tables; # 查看表 create database XXXX charset utf8; #創(chuàng)建數(shù)據(jù)庫(kù)，utf8格式 drop database XXXX; #刪除數(shù)據(jù)庫(kù) show create database XXXXX; #查看創(chuàng)建數(shù)據(jù)庫(kù)的 show create table tablename; #查看創(chuàng)建的表 description tablenmae； #查看表結(jié)構(gòu) 表結(jié)構(gòu)說(shuō)明：
使用mysqladmin管理配置mairadb
#自增長(zhǎng) auto_increment #類型 Type #非空 not null #默認(rèn)值 default 'xx' #唯一 unique #指定字符集 charset #主鍵 primary key #外鍵 增加兩個(gè)表之間的聯(lián)系 增加表：
create table students( id int auto_increment primary key， name varchar(10) not null， sex varchar(3) default '女', address varchar(50), phone int not null unique, age， ); =============================== create table scores( id int auto_increnent primary key, s_id int not null, grade float not null, ); 刪除表
drop table tablename; truncate tablename;#快速刪除表 增
insert into student (name,money,sex,phone) values ('hk',10000,'男',188); insert into student values('','小明',100,'',120); 改
update student set money=100；#不指定條件，修改所有 update student set money=110 where name='hk';#只改hk 查： select * from students limit 1,5; #從第幾條開(kāi)始，下面的x條，不包含開(kāi)始的那一條 SELECT * from students limit 5;查詢5條 SELECT id,stu_name,sex,money,phone from students;#指定查詢的字段 SELECT * from students;#查詢所有的數(shù)據(jù) SELECT * from students where sex='男';#指定條件 SELECT * from students where sex='男' and money>100; #多個(gè)條件，必須同時(shí)滿足 SELECT * from students where sex='男' or sex='未知' ; #多個(gè)條件，有一個(gè)滿足即可 SELECT * from students where sex !='男'; #<>也是不等于 SELECT * FROM students where addr like '%東京%';#模糊匹配，%代表的是通配符，必須得用like SELECT * from students a where a.stu_name like '姚_';#_通配符表示任意一個(gè)單字符，姚字后面只能跟一個(gè)字 SELECT a.stu_name '學(xué)生名稱',a.phone '學(xué)生電話' from students as a where a.stu_name='姚遠(yuǎn)';#給表起別名,as可以省略 SELECT * from students a where a.stu_name in ('×××','林倩','林遠(yuǎn)');# in SELECT * from students a where a.money BETWEEN 1000 and 10000;#在什么什么之間的數(shù)據(jù) SELECT * from students ORDER BY money desc; #order by xxx desc，根據(jù)哪個(gè)字段繼續(xù)排序，默認(rèn)是升序， 降序是desc，升序asc SELECT * from students a where a.addr = '' or a.addr is null; #查詢字段為空的數(shù)據(jù) SELECT DISTINCT a.money from students a ;#去重 SELECT COUNT(*) '學(xué)生人數(shù)' from students where sex='女'; #統(tǒng)計(jì)行數(shù) SELECT MAX(a.money) 錢最多 from students a; #最大值 SELECT min(money) 錢最少 from students;#最小值 SELECT AVG(a.money) 平均多少錢 from students a; #平均數(shù) SELECT sum(a.money) 總共多少錢 from students a;#總和 SELECT sex 性別,count(*) 人數(shù) from students GROUP BY sex; #分組 四、mysqladmin操作
